DB-Object/lib/DB/Object/Fields/Field.pm
##----------------------------------------------------------------------------
## Database Object Interface - ~/lib/DB/Object/Fields/Field.pm
## Version v1.1.1
## Copyright(c) 2023 DEGUEST Pte. Ltd.
## Author: Jacques Deguest <jack@deguest.jp>
## Created 2020/01/01
## Modified 2024/01/03
## All rights reserved
##
##
## This program is free software; you can redistribute it and/or modify it
## under the same terms as Perl itself.
##----------------------------------------------------------------------------
package DB::Object::Fields::Field;
BEGIN
{
use strict;
use warnings;
use common::sense;
use parent qw( Module::Generic );
use vars qw( $VERSION );
use DB::Object::Fields::Overloaded;
use Devel::Confess;
use Module::Generic::Array;
use overload (
'""' => 'as_string',
'bool' => sub{1},
'+' => sub{ &_op_overload( @_, '+' ) },
'-' => sub{ &_op_overload( @_, '-' ) },
'*' => sub{ &_op_overload( @_, '*' ) },
'/' => sub{ &_op_overload( @_, '/' ) },
'%' => sub{ &_op_overload( @_, '%' ) },
'<' => sub{ &_op_overload( @_, '<' ) },
'>' => sub{ &_op_overload( @_, '>' ) },
'<=' => sub{ &_op_overload( @_, '<=' ) },
'>=' => sub{ &_op_overload( @_, '>=' ) },
'!=' => sub{ &_op_overload( @_, '<>' ) },
'<<' => sub{ &_op_overload( @_, '<<' ) },
'>>' => sub{ &_op_overload( @_, '>>' ) },
'lt' => sub{ &_op_overload( @_, '<' ) },
'gt' => sub{ &_op_overload( @_, '>' ) },
'le' => sub{ &_op_overload( @_, '<=' ) },
'ge' => sub{ &_op_overload( @_, '>=' ) },
'ne' => sub{ &_op_overload( @_, '<>' ) },
'&' => sub{ &_op_overload( @_, '&' ) },
'^' => sub{ &_op_overload( @_, '^' ) },
'|' => sub{ &_op_overload( @_, '|' ) },
'==' => sub{ &_op_overload( @_, '=' ) },
'eq' => sub{ &_op_overload( @_, 'IS' ) },
# Full Text Search operator
'~~' => sub{ &_op_overload( @_, '@@' ) },
fallback => 1,
);
use Want;
our $VERSION = 'v1.1.1';
};
use strict;
use warnings;
sub init
{
my $self = shift( @_ );
$self->{check_name} = undef;
$self->{comment} = undef;
$self->{datatype} = undef;
$self->{default} = undef;
$self->{foreign_name} = undef;
$self->{index_name} = undef;
$self->{is_array} = undef;
$self->{is_check} = undef;
$self->{is_foreign} = undef;
$self->{is_nullable} = undef;
$self->{is_primary} = undef;
$self->{is_unique} = undef;
$self->{name} = undef;
$self->{pos} = undef;
$self->{prefixed} = 0;
$self->{query_object} = undef;
$self->{size} = undef;
$self->{table_object} = undef;
$self->{type} = undef;
$self->{_init_params_order} = [qw( table_object query_object default pos type prefixed name )];
$self->{_init_strict_use_sub} = 1;
$self->SUPER::init( @_ ) || return( $self->pass_error );
$self->{_fields} = [qw(
check_name comment datatype default foreign_name index_name is_array is_check
is_foreign is_nullable is_primary is_unique name pos prefixed size type
)];
return( $self->error( "No table object was provided." ) ) if( !$self->{table_object} );
return( $self->error( "Table object provided is not an object." ) ) if( !$self->_is_object( $self->{table_object} ) );
return( $self->error( "Table object provided is not a DB::Object::Tables object." ) ) if( !$self->{table_object}->isa( 'DB::Object::Tables' ) );
return( $self->error( "No name was provided for this field." ) ) if( !$self->{name} );
$self->{trace} = $self->_get_stack_trace;
return( $self );
}
sub as_string { return( shift->name ); }
sub clone
{
my $self = shift( @_ );
# Fields to clone: we explicitly want to avoid cloning query_object, because it is not necessary and because it contains an object to the database connection that we do not want to clone.
# Likewise, the query object, we do not want to clone either.
# my $keys = [qw( default is_nullable name pos prefixed size type )];
my $keys = $self->_fields;
my $hash = {};
@$hash{ @$keys } = @$self{ @$keys };
# $hash->{datatype} = {};
# for( qw( alias constant name re type ) )
# {
# $hash->{datatype}->{ $_ } = $self->datatype->$_;
# }
my $dt = $self->datatype;
$hash->{datatype} =
{
alias => $dt->alias->clone,
constant => $dt->{constant},
name => $dt->{name},
re => $dt->{re},
type => $dt->{type},
};
$self->_load_class( 'Clone' ) || return( $self->pass_error );
my $copy = Clone::clone( $hash );
$copy->{query_object} = $self->query_object;
$copy->{table_object} = $self->table_object;
my $new = $self->new( %$copy, debug => $self->debug ) || return( $self->pass_error );
return( $new );
}
sub check_name { return( shift->_set_get_scalar( 'check_name', @_ ) ); }
sub comment { return( shift->_set_get_scalar( 'comment', @_ ) ); }
# A data type constant
# sub constant { return( shift->_set_get_hash_as_object( 'constant', @_ ) ); }
sub constant { return( shift->datatype->constant( @_ ) ); }
sub database { return( shift->database_object->database ); }
sub database_object { return( shift->table_object->database_object ); }
sub datatype { return( shift->_set_get_class( 'datatype', {
alias => { type => 'array_as_object' },
constant => { type => 'scalar' },
name => { type => 'scalar_as_object' },
re => { type => 'object', package => 'Regexp' },
type => { type => 'scalar_as_object' },
}, @_ ) ); }
sub default { return( shift->_set_get_scalar( 'default', @_ ) ); }
sub foreign_name { return( shift->_set_get_scalar( 'foreign_name', @_ ) ); }
sub first { return( shift->_find_siblings(1) ); }
sub index_name { return( shift->_set_get_scalar( 'index_name', @_ ) ); }
sub is_array { return( shift->_set_get_boolean( 'is_array', @_ ) ); }
sub is_check { return( shift->_set_get_boolean( 'is_check', @_ ) ); }
sub is_foreign { return( shift->_set_get_boolean( 'is_foreign', @_ ) ); }
sub is_nullable { return( shift->_set_get_boolean( 'is_nullable', @_ ) ); }
sub is_primary { return( shift->_set_get_boolean( 'is_primary', @_ ) ); }
sub is_unique { return( shift->_set_get_boolean( 'is_unique', @_ ) ); }
sub last
{
my $self = shift( @_ );
my $fields = $self->table_object->fields;
my $pos = scalar( keys( %$fields ) );
return( $self->_find_siblings( $pos ) );
}
sub name
{
my $self = shift( @_ );
no overloading;
if( @_ )
{
$self->{name} = shift( @_ );
}
my $name = $self->{name};
my $trace = $self->_get_stack_trace;
my $alias = $self->query_object->table_alias;
if( $self->{prefixed} )
{
my @prefix = ();
if( length( $alias ) )
{
CORE::push( @prefix, $alias );
}
else
{
# if the value is higher than 1, we also add the database name as a prefix
# For example $tbl->fields->some_field->prefixed(2)->name
push( @prefix, $self->database ) if( $self->{prefixed} > 2 );
push( @prefix, $self->table_object->schema ) if( $self->{prefixed} > 1 && CORE::length( $self->table_object->schema ) );
push( @prefix, $self->table );
}
push( @prefix, $name );
return( join( '.', @prefix ) );
}
else
{
return( $name );
}
}
sub next
{
my $self = shift( @_ );
return( $self->_find_siblings( $self->pos + 1 ) );
}
sub pos { return( shift->_set_get_scalar( 'pos', @_ ) ); }
sub prefixed
{
my $self = shift( @_ );
if( @_ )
{
$self->{prefixed} = ( $_[0] =~ /^\d+$/ ? $_[0] : ( $_[0] ? 1 : 0 ) );
}
else
{
$self->{prefixed} = 1;
}
return( want( 'OBJECT' ) ? $self : $self->{prefixed} );
}
sub prev
{
my $self = shift( @_ );
return( $self->_find_siblings( $self->pos - 1 ) );
}
sub query_object { return( shift->_set_get_object_without_init( 'query_object', 'DB::Object::Query', @_ ) ); }
sub schema { return( shift->table_object->schema ); }
sub size { return( shift->_set_get_number( 'size', @_ ) ); }
sub table { return( shift->table_object->name ); }
sub table_name { return( shift->table_object->name ); }
sub table_object { return( shift->_set_get_object_without_init( 'table_object', 'DB::Object::Tables', @_ ) ); }
sub type { return( shift->_set_get_scalar( 'type', @_ ) ); }
sub _fields { return( shift->_set_get_array_as_object( '_fields', @_ ) ); }
sub _find_siblings
{
my $self = shift( @_ );
my $pos = shift( @_ );
return( $self->error( "No field position provided." ) ) if( !CORE::length( $pos ) );
return if( $pos < 0 );
my $fields = $self->table_object->fields;
my $next_field;
foreach my $f ( sort{ $fields->{ $a } <=> $fields->{ $b } } keys( %$fields ) )
{
if( $fields->{ $f } == $pos )
{
$next_field = $f;
CORE::last;
}
}
return if( !defined( $next_field ) );
my $o = $self->table_object->fields_object->_initiate_field_object( $next_field ) ||
return( $self->pass_error( $self->table_object->fields_object->error ) );
return( $o );
}
# Ref:
# <https://www.postgresql.org/docs/10/functions-comparison.html>
# <https://www.postgresql.org/docs/10/functions-math.html>
# <https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html>
# <https://sqlite.org/lang_expr.html>
sub _op_overload
{
my( $self, $val, $swap, $op ) = @_;
if( $self->_is_a( $val => 'DB::Object::IN' ) )
{
return( $val->_opt_overload( $self, 1, $op ) );
}
my $field = $self->name;
my $map =
{
'!=' => '<>',
'lt' => '<',
'gt' => '>',
'le' => '<=',
'ge' => '>=',
# '=' works for all types, but IS does not work with everything.
# For example:
# select * from ip_table where ip_addr IS inet '192.168.2.12' OR inet '192.168.2.12' << ip_addr
# does not work, but
# select * from ip_table where ip_addr = inet '192.168.2.12' OR inet '192.168.2.12' << ip_addr
# works better
'==' => '=',
};
$op = $map->{ $op } if( exists( $map->{ $op } ) );
my $dbo = $self->database_object;
my $qo = $self->query_object;
my $placeholder_re = $dbo->_placeholder_regexp;
my $const = $self->datatype->constant;
# $op = 'IS' if( $op eq '=' and $val eq 'NULL' );
# If the value specified in the operation is a placeholder, or a field object or a statement object, we do not want to quote process it
unless( $val =~ /^$placeholder_re$/ ||
( $self->_is_object( $val ) &&
(
$val->isa( 'DB::Object::Fields::Field' ) ||
$val->isa( 'DB::Object::Statement' )
)
) ||
$dbo->placeholder->has( \$val ) ||
$self->_is_scalar( $val ) ||
uc( $val ) eq 'NULL' )
{
$val = $dbo->quote( $val, $const ) if( $dbo );
}
my $types;
# If the value is a statement object, stringify it, surround it with parenthesis and use it
if( $self->_is_a( $val, 'DB::Object::Statement' ) )
{
$self->messagec( 5, "Merging {green}", $val->query_object->elements->length, "{/} elements from this statement object associated with out field {green}", $self->as_string, "{/}" );
$qo->elements->merge( $val->query_object->elements );
$val = '(' . $val->as_string . ')';
}
elsif( $dbo->placeholder->has( $self->_is_scalar( $val ) ? $val : \$val ) )
{
$types = $dbo->placeholder->replace( $self->_is_scalar( $val ) ? $val : \$val );
}
# A placeholder, but don't know the type
elsif( $val =~ /^$placeholder_re$/ )
{
$types = Module::Generic::Array->new( [''] );
}
elsif( $self->_is_scalar( $val ) )
{
$val = $$val;
}
# return( DB::Object::Fields::Overloaded->new(
# expression =>
# (
# $swap
# ? "${val} ${op} ${field}"
# : "${field} ${op} ${val}"
# ),
# field => $self,
# # binded => ( $val =~ /^$placeholder_re$/ || $types ) ? 1 : 0,
# ( $val =~ /^$placeholder_re$/ ? ( placeholder => $val ) : () ),
# type => $self->type,
# # query_object => $self->query_object,
# debug => $self->debug,
# ( $val !~ /^$placeholder_re$/ ? ( value => $val ) : () ),
# # binded_offset => ( $val =~ /^$placeholder_re$/ && defined( $+{offset} ) ) ? ( $+{offset} - 1 ) : undef,
# # types => $types,
# ) );
my $over = DB::Object::Fields::Overloaded->new(
expression =>
(
$swap
? "${val} ${op} ${field}"
: "${field} ${op} ${val}"
),
field => $self,
# binded => ( $val =~ /^$placeholder_re$/ || $types ) ? 1 : 0,
( $val =~ /^$placeholder_re$/ ? ( placeholder => $val ) : () ),
# Actually type() will return us the actual data type, not the driver constant
# type => $self->type,
( defined( $const ) ? ( type => $const ) : () ),
query_object => $qo,
debug => $self->debug,
( $val !~ /^$placeholder_re$/ ? ( value => $val ) : () ),
# binded_offset => ( $val =~ /^$placeholder_re$/ && defined( $+{offset} ) ) ? ( $+{offset} - 1 ) : undef,
# types => $types,
);
return( $over );
}
1;
# NOTE: POD
__END__
=encoding utf8
=head1 NAME
DB::Object::Fields::Field - Table Field Object
=head1 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 L<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 = ?)
=head1 VERSION
v1.1.1
=head1 DESCRIPTION
This is a table field object as instantiated by L<DB::Object::Fields>
=head1 CONSTRUCTOR
=head2 new
Takes an hash or hash reference of parameters and this will create a new L<DB::Object::Fields::Field> object.
=over 4
=item * C<check_name>
Specifies the name of the check constraint associated wit this field.
=item * C<comment>
Specifies the field comment, if any.
=item * C<datatype>
Specifies an hash of key-value pairs, namely: C<name>, C<constant>, C<type> and C<re>
=item * C<debug>
Toggles debug mode on/off
=item * C<default>
Specifies the default field value.
=item * C<foreign_name>
Specifies the name of the foreign key constraint associated wit this field.
=item * C<index_name>
Specifies the index name to which this field is related.
=item * C<is_array>
Specifies a boolean value whether the field value represents an array or not.
=item * C<is_check>
Specifies a boolean value whether the field is associated with a check constraint or not.
=item * C<is_foreign>
Specifies a boolean value whether the field is associated with a foreign key constraint or not.
=item * C<is_nullable>
Specifies a boolean value whether the field value can be null or not.
=item * C<is_primary>
Specifies a boolean value whether the field is the primary key for its table or not.
=item * C<is_unique>
Specifies a boolean value whether the field is part of a unique index or not.
=item * C<name>
The table column name.
An error will be returned if this value is not provided upon instantiation.
=item * C<pos>
The table column position in the table.
=item * C<prefixed>
Defaults to 0
=item * C<query_object>
The L<DB::Object::Query> object.
=item * C<size>
Set the field size, such as for varchar.
Defaults to C<undef>
=item * C<table_object>
The L<DB::Object::Tables> object.
An error will be returned if this value is not provided upon instantiation.
=item * C<type>
The column data type.
=back
=head1 METHODS
=head2 as_string
This returns the name of the field, possibly prefixed
This is also called to stringify the object
print( "Field is: $field\n" );
=head2 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 L<query object|DB::Object::Query> and the L<table object|DB::Object::Tables>
=head2 constant
A data type constant set by L<DB::Object::Table/structure>. This helps determine how to deal with some fields.
This is an hash object that contains 3 properties:
=over 4
=item * C<constant>
An integer set by the database driver to represent the constant
=item * C<name>
The constant name, e.g. C<PG_JSONB>
=item * C<type>
The data type, e.g. C<jsonb>
=back
=head2 check_name
Sets or gets the optional name of the check constraint associated with this field.
=head2 comment
Sets or gets the optional comment that may have been set for this table field.
=head2 database
Sets or gets the name of the database this field is attached to.
=head2 database_object
Returns the database object, ie the one used to make sql queries
=head2 default
Sets or gets the default value, if any, for that field.
=head2 foreign_name
Sets or gets the optional name of the foreign key constraint associated with this field.
=head2 first
Returns the first field in the table.
=head2 index_name
Sets or gets the index name to which this field is related. Defaults to C<undef>
=head2 is_array
Sets or gets true if the field is an array, or false otherwise.
=head2 is_check
Sets or gets true if the field is associated with a check constraint, or false otherwise.
=head2 is_foreign
Sets or gets true if the field is associated with a foreign key constraint, or false otherwise.
=head2 is_nullable
Sets or gets true if the field can be null, or false otherwise.
=head2 is_primary
Sets or gets true if the field is the primary key of the table, or false otherwise.
=head2 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 L<index_name|/index_name>
=head2 last
Returns the last field in the table.
=head2 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 B<prefixed>. See below.
=head2 next
Returns the next field object.
=head2 pos
Sets or gets the position of the field in the table. This is an integer starting from 1.
=head2 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
B<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.
=head2 prev
Returns the previous field object.
=head2 query_object
Sets or gets the query object (L<DB::Object::Query> or one of its descendant)
=head2 schema
Returns the table schema to which this field is attached.
=head2 size
Sets or gets the size of the field when appropriate, such as when the type is C<varchar> or C<char>
=head2 table
Returns the table name for this field.
=head2 table_name
Same as above. This returns the table name.
=head2 table_object
Sets or gets the table object which is a L<DB::Object::Tables> object.
=head2 type
Returns the field type such as C<jsonb>, C<json>, C<varchar>, C<integer>, etc.
See also L</constant> for an even more accurate data type, and the driver associated constant that is used for binding values to placeholders.
=head2 _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.
=head1 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 C<==> would become C<=>:
$f == 'NULL' # dummy = NULL
but, if you use perl's C<eq> instead of C<==>, 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 C<=~> and C<!~> operators cannot be overloaded in perl, so for regular expressions, use the C<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 C<?> 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 C<~~> for SQL Full Text Search and it would be converted into C<@@>:
Let's imagine a table C<articles> in a L<PostgreSQL database|https://www.postgresql.org/docs/current/textsearch.html>, 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 C<WHERE> clause, such as:
WHERE websearch_to_tsquery(?) @@ ts
See L<PostgreSQL documentation|https://www.postgresql.org/docs/current/textsearch.html> for more details.
but, under L<SQLite|https://www.sqlite.org/fts5.html>, 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 L<SQLite documentation|https://www.sqlite.org/fts5.html> for more details.
and, in a L<MySQL database|https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html>, 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 L<MySQL documentation|https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html> for more details.
=head1 SEE ALSO
L<perl>
=head1 AUTHOR
Jacques Deguest E<lt>F<jack@deguest.jp>E<gt>
=head1 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.
=cut