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

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:

+, -, *, /, %, <, <=, >, >=, !=, <<, >>, 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