DB::Object::Fields::Field - Table Field Object
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 = ?)
v1.0.1
This is a table field object as instantiated by DB::Object::Fields
Takes an hash or hash reference of parameters and this will create a new DB::Object::Fields::Field object.
Toggles debug mode on/off
The table column name.
An error will be returned if this value is not provided upon instantiation.
The table column position in the table.
Defaults to 0
The DB::Object::Query object.
The DB::Object::Tables object.
The column data type.
This returns the name of the field, possibly prefixed
This is also called to stringify the object
print( "Field is: $field\n" );
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:
An integer set by the database driver to represent the constant
The constant name, e.g. PG_JSONB
PG_JSONB
The data type, e.g. jsonb
jsonb
Returns the name of the database this field is attached to.
Returns the database object, ie the one used to make sql queries
Returns the default value, if any, for that field.
Returns the first field in the table.
Returns the last field in the table.
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.
Returns the next field object.
Returns the position of the field in the table. This is an integer starting from 1.
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.
Returns the previous field object.
The query object (DB::Object::Query or one of its descendant)
Returns the table schema to which this field is attached.
Returns the table name for this field.
Same as above. This returns the table name.
Returns the table object which is a DB::Object::Tables object.
Returns the field type such as jsonb, Cjson>, varchar, integer, etc.
varchar
integer
See also "constant" for an even more accurate data type, and the driver associated constant that is used for binding values to placeholders.
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.
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:
==
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:
=~
!~
REGEXP
$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 == '?'
perl
Jacques Deguest <jack@deguest.jp>
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.
To install DB::Object, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DB::Object
CPAN shell
perl -MCPAN -e shell install DB::Object
For more information on module installation, please visit the detailed CPAN module installation guide.