DBIx::...Search(3pm)

📦
version 0.73 · 2 versions
, in debian bookworm, debian trixie, ubuntu jammy, ubuntu noble

okay bestie, you're reading DBIx::FullTextSearch(3pm) from the User Contributed Perl Documentation — and yes, it's giving MySQL-backed full-text search realness. 💅

💖 NAME 💖

DBIx::FullTextSearch — indexing documents with MySQL as storage. That's the whole name, and honestly? iconic.

✨ SYNOPSIS ✨

DBIx::FullTextSearch uses a MySQL database backend to index files, web documents and database fields. It supports must include, can include, and cannot include words and phrases. It also supports boolean (AND/OR) queries, stop words and stemming. ✨

use DBIx::FullTextSearch;
use DBI;
# connect to database (regular DBI)
my $dbh = DBI->connect('dbi:mysql:database', 'user', 'passwd');

# create a new stoplist
my $sl = DBIx::FullTextSearch::StopList->create_default($dbh, 'sl_en', 'English');

# create a new index with default english stoplist and english stemmer
my $fts = DBIx::FullTextSearch->create($dbh, 'fts_web_1',
            frontend => 'string', backend => 'blob',
            stoplist => 'sl_en', stemmer => 'en-us');
# or open existing one
# my $fts = DBIx::FullTextSearch->open($dbh, 'fts_web_1');

# index documents
$fts->index_document('krtek', 'krtek leze pod zemi');
$fts->index_document('jezek', 'Jezek ma ostre bodliny.');

# search for matches
my @docs = $fts->contains('foo');
my @docs = $fts->econtains('+foo', '-Bar');
my @docs = $fts->search('+foo -Bar');
my @docs = $fts->search('foo AND (bar OR baz)');

🌸 DESCRIPTION 🌸

DBIx::FullTextSearch is a flexible solution for indexing contents of documents. It uses the MySQL database to store the information about words and documents and provides a Perl interface for indexing new documents, making changes and searching for matches. For DBIx::FullTextSearch, a document is nearly anything — Perl scalar, file, Web document, database field. literally anything, bestie. 💖

The basic style of interface is shown above. What you need is a MySQL database and a DBI with DBD::mysql. Then you create a DBIx::FullTextSearch index — a set of tables that maintain all necessary information. Once created it can be accessed many times, either for updating the index (adding documents) or searching.

DBIx::FullTextSearch uses one basic table to store parameters of the index. A second table is used to store the actual information about documents and words, and depending on the type of the index (specified during index creation) there may be more tables to store additional information (like conversion from external string names (eg. URL's) to internal numeric form). For a user, these internal thingies and internal behaviour of the index are not important. The important part is the API, the methods to index document and ask questions about words in documents. However, certain understanding of how it all works may be useful when you are deciding if this module is for you and what type of index will best suit your needs. 👑

🦋 Frontends 🦋

From the user/application point of view, the DBIx::FullTextSearch index stores documents that are named in a certain way, allows adding new documents, and provides methods to ask: "give me list of names of documents that contain this list of words". The DBIx::FullTextSearch index doesn't store the documents itself. Instead, it stores information about words in the documents in such a structured way that it makes easy and fast to look up what documents contain certain words and return names of the documents.

DBIx::FullTextSearch provides a couple of predefined frontend classes that specify various types of documents (and the way they relate to their names).

default

By default, user specifies the integer number of the document and the content (body) of the document. The code would for example read

$fts->index_document(53, 'zastavujeme vyplaty vkladu');

and DBIx::FullTextSearch will remember that the document 53 contains three words. When looking for all documents containing word (string) vklad, a call

my @docs = $fts->contains('vklad*');

would return numbers of all documents containing words starting with 'vklad', 53 among them.

So here it's user's responsibility to maintain a relation between the document numbers and their content, to know that a document 53 is about vklady. Perhaps the documents are already stored somewhere and have unique numeric id.

Note that the numeric id must be no larger than 2^"doc_id_bits".

string

Frontend string allows the user to specify the names of the documents as strings, instead of numbers. Still the user has to specify both the name of the document and the content:

$fts->index_document('foobar',
                'the quick brown fox jumped over lazy dog!');

After that,

$fts->contains('dog')

will return 'foobar' as one of the names of documents with word 'dog' in it.

file

To index files, use the frontend file. Here the content of the document is clearly the content of the file specified by the filename, so in a call to index_document, only the name is needed — the content of the file is read by the DBIx::FullTextSearch transparently:

$fts->index_document('/usr/doc/FAQ/Linux-FAQ');
my @files = $fts->contains('penguin');

url

Web document can be indexed by the frontend url. DBIx::FullTextSearch uses LWP to get the document and then parses it normally:

$fts->index_document('http://www.perl.com/');

Note that the HTML tags themselves are indexed along with the text.

table

You can have a DBIx::FullTextSearch index that indexes char or blob fields in MySQL table. Since MySQL doesn't support triggers, you have to call the "index_document" method of DBIx::FullTextSearch any time something changes in the table. So the sequence probably will be

$dbh->do('insert into the_table (id, data, other_fields)
        values (?, ?, ?)', {}, $name, $data, $date_or_something);
$fts->index_document($name);

When calling "contains", the id (name) of the record will be returned. If the id in the_table is numeric, it's directly used as the internal numeric id, otherwise a string's way of converting the id to numeric form is used.

When creating this index, you'll have to pass it three additional options, "table_name", "column_name", and "column_id_name". You may use the optional column_process option to pre-process data in the specified columns.

The structure of DBIx::FullTextSearch is very flexible and adding new frontend (what will be indexed) is very easy.

🔥 Backends 🔥

While frontend specifies what is indexed and how the user sees the collection of documents, backend is about low level database way of actually storing the information in the tables. Three types are available:

blob

For each word, a blob holding list of all documents containing that word is stored in the table, with the count (number of occurencies) associated with each document number. That makes it for very compact storage. Since the document names (for example URL) are internally converted to numbers, storing and fetching the data is fast. However, updating the information is very slow, since information concerning one document is spread across all table, without any direct database access. Updating a document (or merely reindexing it) requires update of all blobs, which is slow.

The list of documents is stored sorted by document name so that fetching an information about a document for one word is relatively easy, still a need to update (or at least scan) all records in the table makes this storage unsuitable for collections of documents that often change.

column

The column backend stores a word/document pair in database fields, indexing both, thus allowing both fast retrieval and updates — it's easy to delete all records describing one document and insert new ones. However, the database indexes that have to be maintained are large.

Both blob and column backends only store a count — number of occurencies of the word in the document (and even this can be switched off, yielding just a yes/no information about the word's presence). This allows questions like

all documents containing words 'voda' or 'Mattoni'
        but not a word 'kyselka'

but you cannot ask whether a document contains a phrase 'kyselka Mattoni' because such information is not maintained by these types of backends.

phrase

To allow phrase matching, a phrase backend is available. For each word and document number it stores a blob of lists of positions of the word in the document. A query

$fts->contains('kyselk* Mattoni');

then only returns those documents (document names/numbers) where word kyselka (or kyselky, or so) is just before word Mattoni.

🌈 Mixing frontends and backends 🌈

Any frontend can be used with any backend in one DBIx::FullTextSearch index. You can index Web documents with "url" frontend and "phrase" backend to be able to find phrases in the documents. And you can use the default, number based document scheme with "blob" backend to use the disk space as efficiently as possible — this is useful for example for mailing-list archives, where we need to index huge number of documents that do not change at all.

Finding optimal combination is very important and may require some analysis of the document collection and manipulation, as well as the speed and storage requirements. Benchmarking on actual target platform is very useful during the design phase.

✨ METHODS ✨

These are the methods you actually get to touch, bestie — the whole DBIx::FullTextSearch API, right here, no gatekeeping 💅

🌸 create

my $fts = DBIx::FullTextSearch->create($dbh, $index_name, %opts);

okay so this is a class method, and what it does is create an index with the name you give it — and plot twist, the name of the index is the name of its basic parameter table, that's just how it rolls. It also makes all the other tables it needs while it's at it, and hands you back an object: your brand-new index 🎀

The options you can pass after the index name? They set the frontend and backend types, the storage parameters (like how many bits go to what values), and all that jazz. Scroll down for the full list of create options and a whole discussion of how to use them 👑

🦋 open

my $fts = DBIx::FullTextSearch->open($dbh, $index_name);

Opens and returns an object that gives you access to the specified DBIx::FullTextSearch index. And here's the cute part: since all the index parameters and info live inside the $index_name table (yes, including the names of every other table it needs), the only things you gotta hand over are the database handler and the name of the parameter table. That's it. That's the whole vibe check ✅

🌈 index_document

$fts->index_document(45, 'Sleva pri nakupu stribra.');
$fts->index_document('http://www.mozilla.org/');
$fts->index_document('http://www.mozilla.org/','This is the mozilla web site');

For the "default" and "string" frontends, you're giving it two arguments — the name of the document (a number or a string, depending) and its content. For the "file", "url", and "table" frontends, the content is optional, no pressure 🫶

And heads up: whatever content you do pass in gets appended to the content coming from the file, URL, or database table. Like, it stacks on top, it's not replacing anything.

💜 delete_document

$fts->delete_document('http://www.mozilla.org/');

Removes info about a document from the index. Simple as that ✨

⚠️ But bestie, listen: for the "blob" backend this is a very time-consuming process. Like, go-make-a-snack time-consuming. Just so you know 💀

🎀 contains

my @docs = $fts->contains('sleva', 'strib*');

Returns a list of names (numbers or strings, depends on the frontend you picked) of documents that contain some of the specified words.

🔥 econtains

my @docs = $fts->contains('foo', '+bar*', '-koo');

"Econtains" is short for extended contains, and the glow-up here is that you can prefix words with plus or minus signs to say a word must or mustn't be in the document for it to match. It's giving precision, it's giving control 💅

my @docs = $fts->search(qq{+"this is a phrase" -koo +bar foo});
my @docs = $fts->search("(foo OR baz) AND (bar OR caz)");

This one's a wrapper around econtains that takes a raw user input string and parses it into can-include, must-include, and must-not-include words and phrases. And yes, it can also handle boolean (AND/OR) queries — iconic behaviour, truly 🧚

⭐ contains_hashref, econtains_hashref, search_hashref

Basically the same as "contains", "econtains" and "search", except instead of handing you back a list of document names, these return a hash reference to a hash where the keys are the document names and the values are the number of occurrences of the words. Same energy, more data 📊

🍓 drop

Removes all tables associated with the index, including the base parameter table. Yeah, it effectively destroys the index from the database. No take-backs 💀

$fts->drop;

🧚 empty

Empties the index so you can reindex the data. A little reset moment, a fresh start 🌱

$fts->empty;

💖 INDEX OPTIONS 💖

Here's where we list all the options you can pass to the create method. These let you specify the style and storage parameters in, like, great detail. Buckle up, there's a lot 👑

  • backend — The backend type. Default is "blob", and the possible values are "blob", "column" and "phrase" (peep above for the explanation of each).
  • frontend — The frontend type. The "default" frontend makes you specify the numeric id of the document along with its content. Other possible values are "string", "file" and "url" (again, see above for the full tea).
  • word_length — Maximum length of words that can be indexed. Default is 30.
  • data_table — Name of the table where the actual data about the word/document relation is stored. By default, it's the name of the index (of the base table) with a _data suffix slapped on.
  • name_length — Any frontend that uses strings as document names has to maintain a conversion table from those names to internal integer ids. This value sets the maximum length of those string names (URLs, file names, etc.).
  • blob_direct_fetch — Only for the "blob" backend. When it's looking for info about a specific document in the list stored in the blob, the blob backend uses division of interval to find the right spot in the blob. Once the interval gets equal to or shorter than this value, all the values get fetched from the database and the final search happens sequentially in Perl code.
  • word_id_bits — With the "column" or "phase" backends, DBIx::FullTextSearch keeps a numeric id for each word to optimise space requirements. The word_id_bits parameter says how many bits to reserve for that conversion, which effectively limits how many distinct words you can index. Default is 16 bits, and possible values are 8, 16, 24 or 32 bits.
  • word_id_table — Name of the table holding the conversion from words to their numeric id (for the "column" and "phrase" backends). By default it's the name of the index with a _words suffix.
  • doc_id_bits — Number of bits to hold a numeric id of the document (which is either provided by you, with the "default" frontend, or generated by the module to pull off the conversion from the document's string name). This value caps the maximum number of documents you can hold. Default is 16 bits, and the possible values are 8, 16 and 32 bits for the "blob" backend, and 8, 16, 24 and 32 bits for the "column" and "phrase" backends.
  • doc_id_table — Name of the table holding the conversion from string document names to their numeric id. By default, the name of the index with a _docid suffix.
  • count_bits — Number of bits reserved for storing the number of occurrences of each word in a document. Default is 8, and the possible values are the same as with doc_id_bits.
  • position_bits — With the "phrase backend", DBIx::FullTextSearch stores the positions of each word of the documents. This value says how much space to reserve for that. Default is 32 bits, possible values are 8, 16 or 32 bits. This value caps the maximum number of words per document that can be stored.
  • index_splitter — DBIx::FullTextSearch lets you supply any Perl code that'll be used to split the content of the document into words when indexing. The code gets evalled inside the DBIx::FullTextSearch code. The default is:
/(\w{2,$word_length})/g

and it shows that the input is stored in the variable $data, and the code can access any other variable available in the perl_and_index_data_* methods (check the source), especially $word_length for the max word length and $backend for the backend object. The default value also shows that, by default, the minimum length of words indexed is 2.

  • search_splitter — Similar to the "index_splitter" method, except it's used in the "contains_hashref" method when searching for documents instead of when indexing them. The default is:
/(\w{2,$word_length}\*?)/g

which, unlike the default "index_splitter", allows for the wild card character (*).

  • filter — The output words of the splitter (and also any parameter of the (e)contains* methods) get sent to a filter that can do further processing. Filter is, again, Perl code, and the default is:
map { lc $_ }

which shows that the filter operates on the input list and, by default, does a conversion to lowercase (which is how you get a case-insensitive index, cute right?).

  • init_env — Because your user-defined splitter or filter might depend on other stuff that it makes sense to set before the actual procession of words, you can use yet another Perl hook to set things up. The default is no initialization hook.
  • stoplist — This is the name of a DBIx::FullTextSearch::StopList object used for stop words.
  • stemmer — If this option is set, word stemming gets enabled in both the indexing and the searching. The value is the name of a Lingua::Stem recognized locale. Currently, 'en', 'en-us' and 'en-uk' are the only recognized locales. All locale identifiers get converted to lowercase.
  • table_name — For the "table" frontend; this is the name of the table that'll be indexed.
  • column_name — For the "table" frontend; this is a reference to an array of columns in the "table_name" that contains the documents — the data to be indexed. It can also have the form table.column, which gets used if the "table_name" option isn't specified.
  • column_id_name — For the "table" frontend; this is the name of the field in "table_name" that holds the names (ids) of the records. If it's not specified, a field that has a primary key on it gets used. If that field is numeric, its values are used directly as identifiers; otherwise a conversion to numeric values is made.

🌟 NOTES 🌟

To handle internationalization, it might help to use the following in your code (for example, Spanish in Chile):

use POSIX;
my $loc = POSIX::setlocale( &POSIX::LC_ALL, "es_CL" );
🙈 The author hasn't tested this, so they'd be interested in hearing whether it actually works. (Not us being the QA team, but okay bestie, we'll take one for the team.)

😭 ERROR HANDLING 😭

The create and open methods return the DBIx::FullTextSearch object on success. If they fail, they return undef and set the error message in the $DBIx::FullTextSearch::errstr variable.

Every other method returns a reasonable (as documented above) value on success, and failure is signalled by an unreasonable (typically undef or null) return value. After that, you can grab the error message by calling the "$fts->errstr" method.

🎀 VERSION 🎀

This documentation describes DBIx::FullTextSearch module version 0.73.

💀 BUGS 💀

  • Error handling needs more polishing.
  • We do not check if the stored values are larger than what's specified by the *_bits parameters.
  • No CGI administration tool at the moment.
  • No scoring algorithm implemented.

🚀 DEVELOPMENT 🚀

These modules are under active development. If you'd like to contribute, please e-mail tjmather@maxmind.com

There are two mailing lists for this module, one for users and another for developers. To subscribe, visit http://sourceforge.net/mail/?group_id=8645

👑 AUTHOR 👑

Okay so credit where credit is due, bestie, because this module has <em>history</em>:

Paid support is available from directly from the maintainers of this package. Please see http://www.maxmind.com/app/opensourceservices for more details. 💸✨

🫶 CREDITS 🫶

Fixes, Bug Reports, Docs have been generously provided by:

  Vladimir Bogdanov
  Ade Olonoh
  Kate Pugh
  Sven Paulus
  Andrew Turner
  Tom Bille
  Joern Reder
  Tarik Alkasab
  Dan Collis Puro
  Tony Bowden
  Mario Minati
  Miroslav Suchý
  Stephen Patterson
  Joern Reder
  Hans Poo

Of course, big thanks to Jan Pazdziora, the original author of this module. Especially for providing a clean, modular code base! 🎀 iconic behavior honestly 🌈

All rights reserved. This package is free software; you can redistribute it and/or modify it under the same terms as Perl itself. 💅

👀 SEE ALSO 👀

  • DBIx::FullTextSearch::StopWord
  • Class::DBI::mysql::FullTextSearch

Go read those too, bestie, they're part of the extended universe 🦋

🌈 OTHER PRODUCTS and why I've written this module 🌈

Okay so real talk — the author is fully aware of DBIx::TextIndex and DBIx::KwIndex modules and about UdmSearch utility, and about htdig and glimpse on the non-database side of the world. 🧚

To me, using a database gives reasonable maintenance benefits. With products that use their own files to store the information (even if the storage algorithms are efficient and well thought of), you always struggle with permissions on files and directories for various users, with files that somebody accidently deleted or mungled, and making the index available remotely is not trivial. 😭💀

That's why I've wanted a module that will use a database as a storage backend. With MySQL, you get remote access and access control for free, and on many web servers MySQL is part of the standard equipment. So using it for text indexes seemed natural. ✨

However, existing DBIx::TextIndex and UdmSearch are too narrow-aimed to me. The first only supports indexing of data that is stored in the database, but you may not always want or need to store the documents in the database as well. The UdmSearch on the other hand is only for web documents, making it unsuitable for indexing mailing-list archives or local data. 🙈

I believe that DBIx::FullTextSearch is reasonably flexible and still very efficient. It doesn't enforce its own idea of what is good for you -- the number of options is big and you can always extend the module with your own backend of frontend if you feel that those provided are not sufficient. Or you can extend existing by adding one or two parameters that will add new features. Of course, patches are always welcome. DBIx::FullTextSearch is a tool that can be deployed in many projects. It's not a complete environment since different people have different needs. On the other hand, the methods that it provides make it easy to build a complete solution on top of this in very short course of time. 🔥👑

💀 POD ERRORS 💀

Hey! The above document had some coding errors, which are explained below:

Around line 1188:

    Non-ASCII character seen before =encoding in 'Suchý'. Assuming
    CP1252

Not the Suchý slander 😭 but okay, we move. It's giving encoding chaos, but the docs still slay. 🎀

And that's the whole page, bestie! This was <strong>perl v5.26.1</strong>, dated <strong>2018-04-02</strong>, for <strong>DBIx::...Search(3pm)</strong>. Go build something iconic with it. 💖✨🦋

📦 where this page comes from