NAME

DB::Object::Fields::Field - Table Field Object

SYNOPSIS

    my $dbh = DB::Object->connect({
        driver => 'Pg',
        conf_file => $conf,
        database => 'my_shop',
        host => 'localhost',
        login => 'super_admin',
        schema => 'auth',
        # debug => 3,
    }) || bailout( "Unable to connect to sql server on host localhost: ", DB::Object->error );
    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

You can also use a DB::Object::Statement as a value in the operation:

    my $tbl = $dbh->services || die( "Unable to get the table object \"services\": ", $dbh->error );
    my $userv_tbl = $dbh->user_services || die( "Unable to get the table object \"user_services\": ", $tbl->->error );
    $tbl->where( $tbl->fo->name == '?' );
    my $sub_sth = $tbl->select( 'id' ) || die( "Unable to prepare the sql query to get the service id: ", $tbl->error );
    $userv_tbl->where(
        $dbh->AND(
            $tbl->fo->user_id == '?',
            $tbl->fo->service_id == $sub_sth
        )
    );
    my $query = $userv_tbl->delete->as_string || die( $tbl->error );

This would yield:

    DELETE FROM user_services WHERE user_id = ? AND name = (SELECT id FROM services WHERE name = ?)

VERSION

    v1.0.1

DESCRIPTION

This is a table field object as instantiated by DB::Object::Fields

CONSTRUCTOR

new

Takes an hash or hash reference of parameters and this will create a new DB::Object::Fields::Field object.

debug

Toggles debug mode on/off

default

name

The table column name.

An error will be returned if this value is not provided upon instantiation.

pos

The table column position in the table.

prefixed

Defaults to 0

query_object

The DB::Object::Query object.

table_object

The DB::Object::Tables object.

An error will be returned if this value is not provided upon instantiation.

type

The column data type.

METHODS

as_string

This returns the name of the field, possibly prefixed

This is also called to stringify the object

    print( "Field is: $field\n" );

constant

A data type constant set by "structure" in DB::Object::Table. This helps determine how to deal with some fields.

This is an hash object that contains 3 properties:

constant

An integer set by the database driver to represent the constant

name

The constant name, e.g. PG_JSONB

type

The data type, e.g. jsonb

database

Returns the name of the database this field is attached to.

database_object

Returns the database object, ie the one used to make sql queries

default

Returns the default value, if any, for that field.

first

Returns the first field in the table.

last

Returns the last field in the table.

name

Returns the field name. This is also what is returned when object is stringified. For example

    my $c = $tbl_object->fo->last_name;
    print( "$c\n" );
    # will produce "last_name"

The output is altered by the use of prefixed. See below.

next

Returns the next field object.

pos

Returns the position of the field in the table. This is an integer starting from 1.

prefixed

Called without argument, this will instruct the field name to be returned prefixed by the table name.

    print( $tbl_object->fo->last_name->prefixed, "\n" );
    # would produce my_shop.last_name

prefixed can also be called with an integer as argument. 1 will prefix it with the table name, 2 with the schema name and 3 with the database name.

prev

Returns the previous field object.

query_object

The query object (DB::Object::Query or one of its descendant)

schema

Returns the table schema to which this field is attached.

table

Returns the table name for this field.

table_name

Same as above. This returns the table name.

table_object

Returns the table object which is a DB::Object::Tables object.

type

Returns the field type such as jsonb, Cjson>, varchar, integer, etc.

See also "constant" for an even more accurate data type, and the driver associated constant that is used for binding values to placeholders.

_find_siblings

Given a field position from 1 to n, this will find and return the field object. It returns undef or empty list if none could be found.

OVERLOADING

The following operators are overloaded:

    +, -, *, /, %, <, <=, >, >=, !=, <<, >>, &, |, ^, ==

Thus a field named "dummy" could be used like:

    $f + 10

which would become:

    dummy + 10

And this works too:

    10 + $f # 10 + dummy

Another example, which works in PostgreSQL:

    $ip_tbl->where( 'inet 192.16.1.20' << $ip_tbl->fo->ip_addr );
    my $ref = $ip_tbl->select->fetchrow_hashref;

The equal operator == would become IS:

    $f == 'NULL' # dummy IS NULL

Note that you have to take care of quotes yourself, because there is no way to tell if the right hand side is a string or a function

    $f == q{'JPY'} # dummy IS 'JPY'

or, to insert a placeholder

    $f == '?' # dummy IS ?
    my $sth = $table->select( $f == '?' ); # SELECT dummy IS ? FROM some_table
    my $row = $sth->exec( 'JPY' )->fetchrow;

of course

    my $sth = $table->select( dummy => '?' );

also works

The =~ and !~ operators cannot be overloaded in perl, so for regular expressions, use the REGEXP function if available, or provided the expression directly as a string:

    $table->select( "currency ~ '^[A-Z]{3}$'" );

If you want to use placeholder in the value provided, you will have to provide a ? in the value next to the operator. This module will not parse the value used with the operation, so if you wanted to use a placeholder in:

    $f == "'JPY'"

Simply provide:

    $f == '?'

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