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( "Is nullable: %s\n", $c->is_nullable );
    printf( "Is primary key: %s\n", $c->is_primary );
    printf( "Is an array: %s\n", $c->is_array );
    printf( "Position: %s\n", $c->pos );
    # For example for varchar, this could be 255 based on the table schema
    printf( "Size: %s\n", $c->size );
    printf( "Table: %s\n", $c->table );
    printf( "Database: %s\n", $c->database );
    printf( "Schema: %s\n", $c->schema );
    printf( "Field comment: %s\n", $c->comment );
    printf( "Constant value: %s\n", $c->datatype->constant ); # 12
    printf( "Constant name: %s\n", $c->datatype->name ); # For example: SQL_VARCHAR
    printf( "Constant type: %s\n", $c->datatype->type ); # varchar
    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.1.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.

• check_name

  Specifies the name of the check constraint associated wit this field.

• comment

  Specifies the field comment, if any.

• datatype

  Specifies an hash of key-value pairs, namely: name, constant, type and re

• debug

  Toggles debug mode on/off

• default

  Specifies the default field value.

• foreign_name

  Specifies the name of the foreign key constraint associated wit this field.

• index_name

  Specifies the index name to which this field is related.

• is_array

  Specifies a boolean value whether the field value represents an array or not.

• is_check

  Specifies a boolean value whether the field is associated with a check constraint or not.

• is_foreign

  Specifies a boolean value whether the field is associated with a foreign key constraint or not.

• is_nullable

  Specifies a boolean value whether the field value can be null or not.

• is_primary

  Specifies a boolean value whether the field is the primary key for its table or not.

• is_unique

  Specifies a boolean value whether the field is part of a unique index or not.

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

• size

  Set the field size, such as for varchar.

  Defaults to undef

• 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" );

clone

Makes a clone of the object and returns it.

However, it does not makes a clone of the entire field object, but instead leaves out the query object and the table object

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

check_name

Sets or gets the optional name of the check constraint associated with this field.

comment

Sets or gets the optional comment that may have been set for this table field.

database

Sets or gets 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

Sets or gets the default value, if any, for that field.

foreign_name

Sets or gets the optional name of the foreign key constraint associated with this field.

first

Returns the first field in the table.

index_name

Sets or gets the index name to which this field is related. Defaults to undef

is_array

Sets or gets true if the field is an array, or false otherwise.

is_check

Sets or gets true if the field is associated with a check constraint, or false otherwise.

is_foreign

Sets or gets true if the field is associated with a foreign key constraint, or false otherwise.

is_nullable

Sets or gets true if the field can be null, or false otherwise.

is_primary

Sets or gets true if the field is the primary key of the table, or false otherwise.

is_unique

Sets or gets true if the field is part of a unique index, or false otherwise.

If it is, check out the value for index_name

last

Returns the last field in the table.

name

Sets or gets 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

Sets or gets 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

Sets or gets the query object (DB::Object::Query or one of its descendant)

schema

Returns the table schema to which this field is attached.

size

Sets or gets the size of the field when appropriate, such as when the type is varchar or char

table

Returns the table name for this field.

table_name

Same as above. This returns the table name.

table_object

Sets or gets the table object which is a DB::Object::Tables object.

type

Returns the field type such as jsonb, json, 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:

    +, -, *, /, %, <, <=, >, >=, !=, <<, >>, lt, gt, le, ge, ne, &, |, ^, ==, eq, ~~

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 =:

    $f == 'NULL' # dummy = NULL

but, if you use perl's eq instead of ==, you would get:

    $f eq '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 = ?
    # or;
    $f eq '?' # dummy IS ?
    my $sth = $table->select( $f eq '?' ); # 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 == '?'

You can use the search operator ~~ for SQL Full Text Search and it would be converted into @@:

Let's imagine a table articles in a PostgreSQL database, such as:

    CREATE TABLE articles (
        id SERIAL PRIMARY KEY,
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        ts TSVECTOR GENERATED ALWAYS AS
            (setweight(to_tsvector('english', coalesce(title, '')), 'A') || 
            setweight(to_tsvector('english', coalesce(content, '')), 'B')) STORED
    );

them you coud do:

    $tbl->where(
        \"websearch_to_tsquery(?)" ~~ $tbl->fo->ts,
    );

and this would create a WHERE clause, such as:

    WHERE websearch_to_tsquery(?) @@ ts

See PostgreSQL documentation for more details.

but, under SQLite, this is not necessary, because the Full Text Search syntax is different:

Create a FTS-enabled virtual table.

    CREATE VIRTUAL TABLE articles 
    USING FTS5(title, content);

then query it:

    SELECT * FROM articles WHERE articles MATCH(?);

See SQLite documentation for more details.

and, in a MySQL database, also unnecessary, because a bit different:

    CREATE TABLE articles (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        FULLTEXT (title,content)
    ) ENGINE=InnoDB;

then:

    SELECT * FROM articles WHERE MATCH(title,content) AGAINST(? IN NATURAL LANGUAGE MODE);

See MySQL documentation for more details.

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