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.1.1

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.

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