Full Text Search and Advanced Search

[Torben]

Finding an item in a large database should not require remembering exactly which field contains a piece of information. mAirList 8.1 introduces a new full-text search and a more powerful Advanced Search for this purpose.

Quick search: one search box, all relevant fields

The ordinary database search is now a full-text-style search. Enter one or more words in the search box and mAirList looks for every word across the available item information, including:

• title, artist, comment, and end type;
• database and external IDs;
• item type and file name; and
• standard attributes.

Words are combined with and semantics: an item must match every word, but the words may be found in different fields. For example, searching for summer live can find an item whose title contains “Summer” and whose comment contains “Live”. The search is case-insensitive.

The exact implementation depends on the database connection. PostgreSQL can use its native, indexed full-text search (see below). Other configurations use a compatible database query. In emulated mode, terms are searched anywhere within the text; the regular fallback uses prefix matching. The full-text search mode is configured in the SQL database connection settings. Native SQL search is available only when the database server supports the required full-text-search setup.

Some database backends also recognize duration-like terms such as 3:30, >3:30, or <5:00 in the quick search. For portable and explicit duration searches, use the Duration controls in Advanced Search.

Configuring full-text search

The full-text search strategy is selected in the database connection properties, in the Full Text Search section. The Mode drop-down offers three choices:

• Disabled keeps the database connection from using a full-text index. Search still works, but the fallback query uses prefix matching for each search word. This is the safest choice when no additional database setup is available.
• Emulated also requires no special database objects. mAirList builds a normal SQL query that searches each word anywhere in the searchable text, using wildcard matching. It is broadly compatible, but can be slower on large databases because the database generally cannot use a normal index efficiently for a leading wildcard.
• SQL native delegates the search to the database server’s native full-text-search implementation. In mAirList 8.1 this is supported for PostgreSQL. Results can be ranked by relevance and the search uses an indexed search vector instead of repeatedly scanning all text columns.

For a PostgreSQL connection, select SQL native and click Set up FTS Tables in the same connection-properties page. The setup creates the item_search table, its index, the functions and triggers that keep the search vector current, and an initial vector for the existing items. The vector includes the main item fields and standard attributes, so later changes to items or attributes are picked up automatically. Run this setup after creating the database, or when enabling native FTS for an existing database; make sure the database user has permission to create these objects.

Native FTS is not just a different spelling of emulated search: it depends on the server-specific schema and indexed search data. If SQL native is selected for an unsupported server type, mAirList logs a warning and falls back to the disabled mode. If the setup cannot be run, choose Emulated for a portable substring search or Disabled for the lighter prefix-based fallback.

Advanced Search: describe exactly what you need

Open Advanced Search from a database window to combine several independent criteria. Every criterion is optional, and all criteria that are filled in are applied together. The keyword field at the top is the same broad search described above, and acts as an initial candidate filter. The remaining fields then narrow that candidate set further.

Text fields

Title, Artist, Comment, and End Type support these conditions:

• equals;
• starts with;
• contains;
• does not equal;
• does not start with; and
• does not contain.

Negative conditions deliberately require the field to contain a value. An item with an empty or missing field does not match “does not contain”, for example. This avoids turning an empty database field into a surprising match for every negative search.

Duration and ramp

Duration accepts an optional minimum and maximum. Enter either bound or both; an empty bound is not used. Ramp works the same way, but checks the ramp cue markers stored for the item. A ramp matches when at least one of the supported ramp cue-position types falls within the requested range.

Standard attributes

The attribute rows are created from the configured standard attributes, so the Advanced Search dialog automatically follows the current database configuration. All attributes are treated as single-line text for searching, regardless of how they are edited elsewhere in mAirList.

In addition to the text and range conditions, attributes support:

• is one of - matches when the attribute equals at least one listed value;
• is none of - matches when the attribute equals none of the listed values.

Enter list values in one field separated by semicolons. Whitespace directly before or after a semicolon is ignored, so these examples are equivalent:

Rock;Pop;Jazz
Rock; Pop; Jazz

The list comparisons are case-insensitive and match complete attribute values. An item without the requested attribute, or with an empty value, does not match either list condition.

Examples

To find live rock tracks between three and five minutes:

1. Enter live in Keywords.
2. Set Artist or Title to the desired text condition if needed.
3. Set Duration minimum to 3:00 and maximum to 5:00.
4. Set the Genre attribute to is one of and enter Rock; Alternative.

To exclude items tagged as jingles or sweepers, set the relevant attribute to is none of and enter Jingle; Sweeper. Items without that attribute are not included by this condition.

For DBServer API and integration users

Complete search queries use the dedicated /search endpoint. A keyword query is sent as term; advanced values use field-specific parameters, for example:

/search?term=live&duration_min=3:00&duration_max=5:00&attribute_Genre=Rock;%20Alternative&attribute_condition_Genre=oneof

Text conditions use parameters such as title_condition=contains, while attribute list conditions use oneof and noneof. The same query format is used by Advanced Search, saved smart folders, and database clients. Results are filtered first and the requested result limit is applied afterward.

The legacy /items?search=... form remains available for existing clients, but complete Advanced Search queries should use /search.