IndexTool

IndexTool.exe (which replaces the now deprecated IndexCheck tool) is a freely available Axiell command-line tool especially for quickly creating full text indexes and it does it substantially faster than Designer can, after you've set the Enable full text index option for all .inf's in your data folder and saved that setting. From the version of July 2023 (for Collections 1.17) it is able to create normal indexes too (for databases that haven't been Full Text-enabled), also faster than Designer can. (So new tables will be created if they don't exist yet.) Full text and non-full text indexes for metadata tables are supported from IndexTool version 7.13.1.6699 (and in Designer too). However, indexed links are not supported by the tool yet, so you should use Designer if this type of index appears in your application (too).
Preferably place the tool files in a separate folder near SQL Server but that is not required. If the operation of the tool is aborted mid-process, it can continue where it left off at a later time because it stores a continuation file in the \data folder of the Collections application so the user must have write/create file access to this folder, or this part of the program will not work. (When you restart an aborted process, provide the same arguments but don't use -r or -x, otherwise it will reset the start priref to 1 instead of using the value from the continuation file.)
The SQL user configured in the database .inf files must have access rights to create tables and install user defined SQL types.

For safety, create a backup of your database first. Then run the tool from a command-line window and specify any of the following options as its arguments (include the dashes).

--help

Show command line help. However, the command IndexTool help or IndexTool --help only shows which so-called verbs (a way to group command-line options) are available, namely:

  • index (default verb, so you don't need to include it on the command-line): run the indexing process.

  • profile: profile management.

  • help: display the available verbs. To get help about all available index or profile arguments, use either:
    indextool index --help
    or
    indextool profile --help
    respectively.

  • version: display IndexTool version information.

On the command-line, a verb (if provided) must be entered between IndexTool and the arguments for the verb.

--version

Show IndexTool version number

-p or
--data-path
(for index verb)

Path to application \data folder. Use this option to specify the path to the Collections \data folder. The user running the tool should preferably have write access to this folder since the tool will attempt to create a file in this location (continuation.json).

-r or
--reindex
(for index verb)

Indexes all records in the database. This ignores any continuation information that might exist for this database. This option also skips rebuilding the SQL database schema for the indexes. Omit this option to resume an aborted indexing operation.

Use this option when you don't want to erase the old data. The result is the same as with -x, but -r might be a little bit slower. -x deletes the tables, -r overwrites the tables.

-x or
--drop-index
(for index verb)

Drops and recreates the full text index tables (in case the created indexes need to be redone for some reason). This option instructs the tool to drop any existing full text indexes and recreate the full text index table for every database table that it is processing. Use this option to start the indexing process from the very beginning with fresh and empty tables.
For non-full text indexed databases, -x will also empty and rebuild all indexes except the wordlist table: the wordlist table will only be emptied if the tool knows it is reindexing all databases. (Note that the wordlist table truncates words longer than 32 characters, which will be logged.)
This option automatically implies -r so there is no need to specify that option as well when specifying -x. Use this option to rebuild the SQL database schema for the indexes.
When reindexing a single field or just a few fields with a Text or Free text index definition (using the -d option with a tag list) for all records in the database, it is still recommended to use the -x option as well because all fields with index definitions of this type will be reindexed anyway (even if not specified in the tag list) and if all records need to be reindexed, dropping and recreating the full text index table(s) is usually faster than checking and updating the existing ones.
Do not use -x on a live application when users are working in Collections.

-l or
--log-path
(for index verb)

Enable and create log file at this path. Include the desired file name, something like mylog.txt for example. (If you specify the same file name repeatedly, then new log information will be added to the existing file.) By default (if you do not use the option), the tool will create a log file with a name formatted like indextool_<ISOdate>_<time>.log in the tool folder: it will also always print the log to the terminal window before it exits. Use the option to specify your own path and file name of the log file to which to log all tool events. You must have write/create access to that folder.
The tool reports index key data errors and any duplicate keys for unique indexes. Index key data errors are errors about data which does not match the index data type, such as dates in the wrong format, numericals with the wrong decimal character, etc. The log will only contain the number of keys that was sent to each index, but not how many were added or deleted.
If the log ends with something similar to the following (this was a very small test database with a lot of empty databases), the reindexing was successful:
 
2022-06-02 18:37:45.9958 | All indexing operations have concluded...

2022-06-02 18:37:46.0079 | Databases completed successfully: "collect,letters,people,settings,supplang,thesau"

2022-06-02 18:37:46.0079 | Databases skipped/empty: "auction,borrcat,borrower,budget,circuit,collname,conserva,copies,costs, currency,document,exhibit,fees,fines,littheme,loanhist,loans,location, media,orders,orditems,packgtyp,price,rameau,reprlang,reprordr,research, reserv,retention,sericopy,series,statics,stock,subscrip,taxonom,transpor"

2022-06-02 18:37:46.1743 | Wrote continuation list (42) to path "..\data\continuation.json"
 
You may delete that continuation.json file if the reindexing completed fully.

-d or
--database
(for index verb)

Whitespace separated list of one or more database tables. This option lets the user specify which database tables should be included in the indexing process. Typically you want all tables to be indexed and you specify that with an asterisk (*).
However, you can also specify the database tables to be processed as a whitespace separated list of database definition names without the .inf extension (e.g. collect people thesau). Special considerations should be taken for the asterix wildcard (*) which selects all database tables: when dash (-) is used at the end of a database table name it means that table is excluded. For full text indexing, database tables that do not have full text indexing enabled will be automatically excluded as well.
You can also specify tags and a priref range to index, using the following argument format (tags separated by commas):

--database <database name>:<tag_list>:<first_priref-last_priref>

Note that for full text indexing, specifying a tag which has a Text or Free text index definition (so it will be included in the full text index table), will implicitly also include all other tags with a Text or Free text definition in the relevant database table definition during the indexing action, similar to the (--fulltext) option.

To omit the tags, just enter two colons:

-d <database>::<first_priref-last_priref>

Either the first or last record number can be omitted too, like this:

-d <database>:<tags>:<first_priref>

This indexes from the specified record number up to the last in the database table.

-d <database>:<tags>:<-last_priref>

This indexes from record number 1 up to the priref specified: enter a hyphen directly in front of the record number.

-f or
--fulltext
(for index verb)

This option (available from 1.11.1) instructs the tool to only perform Full Text indexing. For Full Text enabled .inf's, using this option is recommended because otherwise indextool will also reindex all irrelevant indexes, wich takes much longer.

This option is inclusive with specified tags for option -d as well, so both can be specified at the same time, e.g.:

-f -d collect:Ds

This indexes all tags destined for full text indexing plus the Ds tag.

--settings

This option (available from 1.11.1) will create a file called indextool.settings (a json file) where some internal values can be adjusted, buffers and such, the tool will pick up the file if it exists or use the defaults if the file doesn't exist. For advanced users only.

ContinuationTimer - By default, the continuation file is updated at the end when the tool exits, either from an unrecoverable error or cleanly when nothing went wrong. The internal setting called ContinuationTimer defaults to zero for the default behaviour, but if you change it, the continuation information will be committed to file based on the value in seconds. So don’t set it at 1 or anything, maybe every 60 seconds is better (or even longer). Use this option only if the continuation file isn't created after an error. Put the value back to zero to disable the in-between continuation info commits.

--log-debug
(for index verb)

This option (available from 1.11.1) will make the tool log a bit more information than it does normally.

--log-trace
(for index verb)

This option (not implemented yet, for future use), will eventually log details about every record.

-b or
--bulk-mode
(for index verb)

This option (available from 1.11.1) will attempt to set the SQL Server database recovery log option to BULK_LOGGED instead of FULL during indexing in order to minimize the SQL server database log file size.

--profile
(for index verb)
and
profile verb arguments --load, --list and --delete

You can create and load profiles. This stores the used command line options in a name file. So if you have a long command line and you know you're going to run it again in the future, you can add --profile <name> and it will be stored so you can run the indextool again with that profile name instead of specifying all arguments again. When you create a profile, only the profile will be created: the index command itself will not be executed, so you will have to load the profile to execute it.
The profile file will be saved in your local user folder (C:\Users\<user name)> with the subfolder path \AppData\Roaming\AxiellIndexTool\Profiles\<file_name>.json.

To manage profiles (list, load, delete) you must use the profile verb (not --profile for the index verb) on the command line:

indextool profile --load <profile_name without extension> will load the specified, earlier created profile and run it. Instead of --load you can also use -l.

indextool profile --list will list the available profiles. --list can be replaced by -e (stands for enumerate).

indextool profile --delete <profile_name without extension> will delete the profile. --delete equals -d.

Examples:

indexTool.exe -p "..\data" -d *
-l "..\logs\indextoollog.txt"

Index all the database tables (this is also the default if -d is not specified) and create a log file. After running the tool, check in SQL Server Management Studio that each .inf now has a full text index table: these have names like dbo.collect_fulltext, dbo.thesau_fulltext, etc. If you already had the relevant database opened in SQL Server Management Studio, you may have to use the Refresh function in there to refresh the list of tables and see the new ones.

indextool.exe -p "..\data" -x -d * -l "..\logs\mylog.txt"

If due to errors not all of the full text indexes have been created, you can try again and add the -x option to drop the earlier created ones and recreate all full text indexes.

indexTool.exe -p "C:\Axiell\data" -d collect people

Index only collect and people.

indexTool.exe -p "C:\Axiell\data" --database event-

Index all database tables except for the event table.

Additional Information:

The tool will automatically shut down when all processing has completed, however the tool can be interrupted at any point and shut down using the F10 key. Ctrl+C will not work and has no effect. From version 1.11.1, the tool also writes a file called indextool_rejected_fields<date>.csv with all the values that failed validation during indexing.
The tool is a fully multi-threaded application and will utilize all available CPU cores, however the tool is not heavy on the CPU as most time is spent transferring data from and to the SQL server. The tool will index two databases concurrently and there is currently no way of specifying any advanced indexing settings.
The user interface is fully resizeable, and the mouse cursor can be used to scroll the list of database tables during the reindexing process.

IndexTool will not do any comparing of existing values in the index to values in records, like the now deprecated IndexCheck tool did: the old data in the indexes will simply be overwritten and the SQL queries which retrieve that data, run for batches of 64 records each, not per key or anything like that. The only communication back from SQL server to IndexTool relates to unique indexes, in which case the query returns how many violations of uniqueness were found.