NAME

DB::Object::Fields - Tables Fields Object Accessor

SYNOPSIS

    my $dbh = DB::Object->connect({
        driver => 'Pg',
        conf_file => $conf,
        database => 'my_shop',
        host => 'localhost',
        login => 'super_admin',
        schema => 'auth',
        unknown_field => 'fatal',
        # debug => 3,
    }) || bailout( "Unable to connect to sql server on host localhost: ", DB::Object->error );
    
    my $tbl = $dbh->some_table || die( "No table \"some_table\" could be found: ", $dbh->error, "\n" );
    my $fo = $tbl->fields_object || die( $tbl->error );
    my $expr = $fo->id == 2;
    print "Expression is: $expr\n"; # Expression is: id = 2

    my $tbl_object = $dbh->customers || die( "Unable to get the customers table object: ", $dbh->error, "\n" );
    my $fields = $tbl_object->fields;
    print( "Fields for table \"", $tbl_object->name, "\": ", Dumper( $fields ), "\n" );
    my $c = $tbl_object->fo->currency;
    print( "Got field object for currency: \"", ref( $c ), "\": '$c'\n" );
    printf( "Name: %s\n", $c->name );
    printf( "Type: %s\n", $c->type );
    printf( "Default: %s\n", $c->default );
    printf( "Position: %s\n", $c->pos );
    printf( "Table: %s\n", $c->table );
    printf( "Database: %s\n", $c->database );
    printf( "Schema: %s\n", $c->schema );
    printf( "Next field: %s (%s)\n", $c->next, ref( $c->next ) );
    print( "Showing name fully qualified: ", $c->prefixed( 3 )->name, "\n" );
    ## would print: my_shop.public.customers.currency
    print( "Trying again (should keep prefix): ", $c->name, "\n" );
    ## would print again: my_shop.public.customers.currency
    print( "Now cancel prefixing at the table fields level.\n" );
    $tbl_object->fo->prefixed( 0 );
    print( "Showing name fully qualified again (should not be prefixed): ", $c->name, "\n" );
    ## would print currency
    print( "First element is: ", $c->first, "\n" );
    print( "Last element is: ", $c->last, "\n" );
    # Works also with the operators +, -, *, /, %, <, <=, >, >=, !=, <<, >>, &, |, ^, ==
    my $table = $dbh->dummy;
    $table->select( $c + 10 ); # SELECT currency + 10 FROM dummy;
    $c == 'NULL' # currency IS NULL

    # if DB::Object unknown_field option is set to fatal, this will die. By default, it will simply be ignored
    my $unknown_field = $tbl->unknown;

VERSION

    v1.2.0

DESCRIPTION

The purpose of this module is to enable access to the table fields as DB::Object::Fields::Field objects.

The way this works is by having "fields_object" in DB::Object::Tables or "fo" in DB::Object::Tables for short, dynamically create a class based on the database name and table name. For example if the database driver were PostgreSQL, the database were my_shop and the table customers, the dynamically created package would become DB::Object::Postgres::Tables::MyShop::Customers. This class would inherit from this package DB::Object::Fields.

Field objects can than be dynamically instantiated by accessing them, such as (assuming the table object $tbl_object here represent the table customers) $tbl_object-fo->last_name>. This will return a DB::Object::Fields::Field object.

A note on the design: there had to be a separate this separate package DB::Object::Fields, because access to table fields is done through the AUTOLOAD and the methods within the package DB::Object::Tables and its inheriting packages would clash with the tables fields. This package has very few methods, so the risk of a sql table field clashing with a method name is very limited. In any case, if you have in your table a field with the same name as one of those methods here (see below for the list), then you can instantiate a field object with:

    $tbl_object->_initiate_field_object( 'last_name' );

If you call an unknown field, its behaviour will change depending on the option value unknown_field of DB::Object upon instantiation:

• ignore (default)

  The unknown field will be ignored and a warning will be emitted that this field does not exist in the given database table.

• fatal or die

  This will trigger a "die" using a Module::Generic::Exception object. So you could catch it like this:

      use Nice::Try;
      
      try
      {
          # $opts contains the property 'unknown_field' set to 'die'
          my $dbh = DB::Object::Postgres->connect( $opts ) || die( "Unable to connect" );
          my $tbl = $dbh->some_table || die( "Unable to get the database table \"some_table\": ", $dbh->error );
          $tbl->where( $dbh->AND(
              $tbl->fo->faulty_field == '?',
              $tbl->fo->status == 'live',
          ) );
          my $ref = $tbl->select->fetchrow_hashref;
      }
      catch( $e isa( 'Module::Generic::Exception' ) )
      {
          die( "Caught error preparing SQL: $e" );
      }
      else
      {
          die( "Caught some other error." );
      }

• code reference

  When the option unknown_field is set to a code reference, this will be executed and passed an hash reference that will contain 3 properties:

  1. table

  The table object

  2. field

  A regular string containing the unknown field name

  3. error

  The error object, which includes the error string and a stack trace

By default, the unknown field will be ignored.

CONSTRUCTOR

new

Creates a new DB::Object::Fields objects. It may also take an hash like arguments, that also are method of the same name.

debug

Toggles debug mode on/off

METHODS

database_object

The database object, which is a DB::Object object or one of its descendant.

new_field

This takes an hash or hash reference of parameters and instantiate a new DB::Object::Fields::Field object and return it.

If an error occurred, it sets an error object and return an empty list in list context or undef in scalar context.

prefixed

This si the prefix level, from 0 to 2.

2 or higher including the database, higher than 1 includes the schema name and above 0 includes the table name. 0 includes nothing.

When this value is changed, it is propagated to all the fields objects.

query_object

The query object, which is a DB::Object::Query object or one of its descendant.

table_object

The query object, which is a DB::Object::Tables object or one of its descendant.

_initiate_field_object

This method is called from AUTOLOAD

Provided with a table column name and this will create a new DB::Object::Fields::Field object and add dynamically the associated method for this column in the current package so that next time, it returns the cached object without using AUTOLOAD

AUTOLOAD

Called with a column name and this will check if the given column name actually exists in this table. If it does, it will call "_initiate_field_object" to instantiate a new field object and returns it.

If the column does not exist, it returns an error.

SEE ALSO

perl

AUTHOR

Jacques Deguest <jack@deguest.jp>

COPYRIGHT & LICENSE

Copyright (c) 2020-2021 DEGUEST Pte. Ltd.

You can use, copy, modify and redistribute this package and associated files under the same terms as Perl itself.

cpanm DB::Object

perl -MCPAN -e shell
install DB::Object