Optional full text indexing for real phrase searching

The new (optional) Full Text index table is available from Axiell Designer version 7.8 and Axiell Collections version 1.14 (and won't break compatibility with older versions Designer but will break compatibility with older versions of Collections) and entails an additional, new SQL database table type, replacing all free text index tables, all non-unique text (term) index tables and the wordlist table by a single so-called Full Text index table per .inf file which will be named after their .inf name, in the name format dbo.<database>_fulltext, dbo.collect_fulltext for example. Uniquely indexed fields will also be indexed in the same Full Text index table for search performance improvement, but unique index tables will remain present and updated as well but these will be used only to check for uniqueness of the relevant field contents on data entry. The advantage of the single Full Text index table being that the SQL queries which are executed under the hood of Collections or the WebAPI or any other search-capable Axiell tools, will become much simpler and efficient and may therefore greatly increase search performance in the relevant indexes. For Full Text indexed databases it also allows Starts with, Contains phrase and Does not contain phrase access point searching on both Text (term) and Free text indexed fields, whereas the normal database structure only allows Contains phrase and Does not contain phrase searching on Text indexes. Moreover (from Collections 1.15), in Full Text indexed database tables you can search for values containing all sorts of punctuation characters (like dots, hyphens, slashes, question marks, etc.) by entering the search key without those punctuation characters (or by entering the wrong ones), as long you are not search a uniquely indexed field: uniquely indexed fields still enforce exact searching on keys with punctuation characters.

Note that importing data via Designer or import.exe does not support this table structure, only importing through Collections, Axiell Migration (an internallly used tool) and importtool.exe is supported.

Setup of the full text index database structure

The index definitions for all free text and non-unique and unique indexes will and must remain present in each .inf. Reindexing one of the free text, unique or non-unique indexes in Designer will add or update the rows for the relevant field tag in the new table. Unique indexes also still have their own tables, which will be updated on reindexing as well. When you'd like to add a field to the Full Text index, just create an index for it as you would normally do and reindex it.
Note that for this functionality, Text and Free text indexes are considered identical and the index Key size is no longer relevant. Only the field Maximum length determines how big a value in a field can get: Full Text indexes will contain the entire value (up to 2GB large) in the stripped and value columns of the table. For uniquely Text indexed fields, the index Key size remains relevant for the old index table though (but not for the field contents indexed in the Full Text table).

In the SQL database, each of the new tables will have the following columns:

The strippedterm column is available from Collections 1.16, the stripped column from 1.15.
Data in that table will look somewhat like the following:

Note that setting this option breaks compatibility with older versions of all Axiell software (except Designer). So using the Full Text option means you'll have to update all Axiell software to the latest version. Also, if you have the Axiell Internet Server and/or WebAPI, changes will have to be made to the configuration of this software too because equals and contains searches behave differently* with the new index type. Please consult the Axiell helpdesk for more information about these consequences before using Full Text indexing.

* In Full Text indexed databases, searching with the equals (=) operator shows different behaviour than in normal (non-Full Text indexed) databases. However, really the only difference is that searching Free text indexes (for long text fields) in a normal database using the equals operator, actually behaves as a contains all search, so there you only need to provide one or two words from the long text field, in random order, to find the record with the long value containing those words. With Full Text indexing though, the equals search behaves more as you would expect, namely to search for an exact match on the search key. So with Full Text indexing you must either provide the entire long value you are searching on or provide just one or two words from the long value, but put them in the right order and allow for words in front, in between or after the searched words, by entering an asterisk before, in between and after those words (and enclose it all by double quotes): it doesn't matter if there's a space or not between the asterisk and the word before or after it, unless you've entered a partial word, in which case the asterisk must be concatenated with the partial word. Of course, with this more limited implementation of the equals search, it might be easier to start using the desired "contains" search in Full Text indexed databases from now on as then you don't need to use truncation characters if your search key isn't the full value.
Also note that in Collections 1.15.1, implicit right truncation (for the full field contents) was enabled for equals searches but this has been turned off again, so if you'd like to truncate an equals search you'll always have to do it explicitly from now on.

The Full Text index structure is reversible, should you want to go back to the old structure. If all you did was mark the Enable full text index checkbox for one or all databases, then just deselect it for all databases and you're fine. If you also reindexed all databases, you'll have to deselect the option for all databases and restore the SQL backup you made earlier. If you didn't make a backup, you'll have to deselect the option for all databases and reindex all databases again (using Designer or IndexTool) to reconstruct the old indexes: this will take much longer than restoring a backup.

From Designer 7.14, the Enable full text index checkbox is available for metadata table definitions too and it behaves the same as for other database definitions. The full text tables are created using the Create missing tables function in Designer and the index is populated using the reindex function. Full text tables are created immediately when the INF's with the full text setting are saved for the first time.

Improving searchability on values with punctuation or diacritical characters from Collections 1.15

In the 1.14 version of Full Text indexed databases, values with specific punctuation and diacritical characters, like in the name "O'Toole, Peter" for example, could only be found when searching on this name including the single quote and comma, so in these cases you always had to know exactly how a name or term is spelled, which was even harder when other special characters are used, like diacritics, brackets or hyphens etc.
That's why from Collections 1.15 you can search on such field values both with or without such special characters (also case-insensitive) and/or use the so-called normalized form of letters with diacritical characters and still find what you're looking for, but you'll have to update your Full Text indexed database to make this possible. This update is mandatory if you already had a Full Text indexed database: if you didn't have a Full Text indexed database yet and you are implementing it for the first time, you already get the updated version immediately.

Via the latest release version of the Axiell IndexTool (which is freely available through our helpdesk) you simply perform this update on your existing Full Text indexed database with data. Do make a backup before you execute indextool.exe, just to be on the safe side.

Log everyone out of Collections and from a command-line simply execute the tool with the -x argument and the appropriate path to the \data folder of your Axiell system, something like:

indextool -p <path to data folder> -x

This will rebuild the Full Text indexes and add a "stripped" column to the tables, containing the stripped version of field values stored in the record with punctuation or special characters (values without such characters won't get a stripped value). Recycle the Collections application pool when the tool is finished. Now whenever the user performs a search, Collections will search both the value and the stripped columns (both contain the full field contents, but one is without punctuation characters). The only exception are uniquely indexed fields: for such fields the stripped version of a term is not used to find records. So if a uniquely indexed object number contains a hyphen for example, like ABC-1234, you cannot find the relevant record by searching on ABC1234, but you can find it by searching on e.g. ABC* or ABC-1234.
The stripped column (and the value column) can index (far) more than the 100 characters that the term column can, so long titles or descriptions are indexed here completely. This means you can search on the full title (using the Equals operator) or on one or more words (even beyond the 100 character limit) using the Contains phrase operator.
A disadvantage may be that the user can't search on special characters in non-unique indexes anymore.

Other Axiell software (besides Collections and Designer) that's been updated to deal with the stripped column, are: IndexTool, WebAPI and Move.

See the Collections 1.15 release notes for more information about the new search capabilities for users.

Performance of certain searches in Full Text indexed databases greatly improved from Collections 1.16

To improve performance of certain searches in Full Text indexed databases, yet another column can be/has been added to the dbo.<database_name>.fulltext tables in the SQL database. This column is named "strippedterm" (not to be confused with the "stripped" column or the "term" column). The difference between the contents of the strippedterm and stripped columns is that the contents of the strippedterm column is truncated to 100 characters (while the stripped and value column may contain longer strings, up to 2GB in fact).

If your filled or empty SQL database is already Full Text enabled, you may (optionally) add the new column to all relevant Full Text indexes by starting Designer in administrator mode (right-click AxiellDesigner.exe in Windows Explorer and select Run as administrator in the pop-up menu), and executing the Create missing tables option on the \data folder (right-click the \data folder in the Designer Application browser and select the option in the pop-up menu). Be sure to log everyone out of Collections before you change the database structure and recycle the application pool afterwards.
Instead of executing the Designer Create missing tables option, you can also run the latest version of indextool.exe with the -x argument and the appropriate path to the \data folder, from the command line, with something like:

indextool -p <path to data folder> -x

If you didn't have a Full Text indexed database yet and you are implementing it for the first time, you already get the updated version immediately.

Other Axiell software (besides Collections and Designer) that's been updated to deal with the strippedterm column, are: IndexTool, WebAPI and the Move server.

The performance gain does not apply to left truncated searches.

See also: using IndexTool.exe