Class FullTextIndex

Namespace: YndigoBlue.Velocity.Model

Assembly: YndigoBlue.Velocity.dll

Represents a full-text index that enables advanced text search capabilities on table columns.

public class FullTextIndex : IEquatable<FullTextIndex>, IComparable<FullTextIndex>

Inheritance: object

FullTextIndex

Implements: IEquatable<FullTextIndex>

IComparable<FullTextIndex>

Remarks

Warning

Full-text indexes are not available in the Community Edition of Velocity. They require the Full Edition.

Full-text indexes enable powerful text search features beyond simple LIKE queries, including:

Searching for words or phrases within text columns
Ranking search results by relevance
Finding word variations (stemming)
Searching across multiple text columns simultaneously

Full-text indexing is database-dependent and may not be supported on all database systems:

SQL Server: Fully supported via CREATE FULLTEXT INDEX
PostgreSQL: Supported via GIN/GiST indexes on tsvector columns
MySQL: Supported via FULLTEXT indexes (InnoDB and MyISAM)
Oracle: Supported via Oracle Text indexes
SQLite: Supported via FTS5
DB2: Supported via Db2 Text Search
Teradata: No native support. Requires the Teradata Full-Text Search (TDFTS) extension to be installed on the Teradata instance

Full-text indexes are created on tables using CreateFullTextIndex(string, IEnumerable<string>). The database creates the index when the schema is built with BuildSchema(Schema, bool, bool).

Performance Note: Full-text indexes can significantly improve search performance for text-heavy columns (like article content, descriptions, or comments), but they require additional storage space and can slow down INSERT/UPDATE operations.

Examples

Creating a full-text index on a single column:

Table articlesTable = schema.CreateTable("articles");
articlesTable.CreateColumn("id", DataType.Integer);
articlesTable.CreateColumn("title", DataType.VarChar, size: 200);
articlesTable.CreateColumn("content", DataType.Clob);

// A single-column primary key is required before a full-text index can be created
articlesTable.CreatePrimaryKey("pk_articles", "id");

// Create full-text index on content column
FullTextIndex contentIndex = articlesTable.CreateFullTextIndex(
    "ft_articles_content",
    [ "content" ]
);

manager.BuildSchema(schema);

// SQL Server: CREATE FULLTEXT INDEX ON articles (content) KEY INDEX PK_articles
// MySQL: CREATE FULLTEXT INDEX ft_articles_content ON articles (content)

Creating a full-text index on multiple columns:

Table productsTable = schema.CreateTable("products");
productsTable.CreateColumn("id", DataType.Integer);
productsTable.CreateColumn("name", DataType.VarChar, size: 100);
productsTable.CreateColumn("description", DataType.VarChar, size: 1000);
productsTable.CreateColumn("features", DataType.Clob);

productsTable.CreatePrimaryKey("pk_products", "id");

// Create full-text index across multiple columns
FullTextIndex productIndex = productsTable.CreateFullTextIndex(
    "ft_products_search",
    [ "name", "description", "features" ]
);

manager.BuildSchema(schema);

// This allows searching across all three columns simultaneously

Searching a full-text index. The same call works on every supported datasource, and the SCORE column is normalized to a 0-100 relevance scale:

Column[] columns = [ articlesTable["id"], articlesTable["title"] ];

using (ResultSet results = manager.FullTextRetrieve(articlesTable, columns, "database performance"))
{
    foreach (Result row in results)
    {
        double score = row.GetFieldDouble("score");
        Console.WriteLine($"[{score:F2}] {row.GetFieldVarChar("title")}");
    }
}

Full-text index on blog posts:

Table postsTable = schema.CreateTable("posts");
postsTable.CreateColumn("id", DataType.Integer);
postsTable.CreateColumn("title", DataType.VarChar, size: 255);
postsTable.CreateColumn("body", DataType.Clob);
postsTable.CreateColumn("tags", DataType.VarChar, size: 500);

postsTable.CreatePrimaryKey("pk_posts", "id");

// Index title, body, and tags for comprehensive search
FullTextIndex postSearchIndex = postsTable.CreateFullTextIndex(
    "ft_posts_search",
    [ "title", "body", "tags" ]
);

manager.BuildSchema(schema);

// Now users can search across titles, content, and tags efficiently

Properties

Columns: Gets the collection of column names included in this full-text index.

Name: Gets the name of the full-text index.

SchemaName: Gets the name of the schema containing this full-text index's table.

SchemaUpdate: Gets or sets whether this full-text index was created during a schema update operation.

TableName: Gets the name of the table this full-text index belongs to.

Methods

CompareTo(FullTextIndex): Compares this full-text index to another by name for ordering purposes.

Equals(object): Determines whether the specified object is equal to the current object.

Equals(FullTextIndex): Compares this full-text index to another by name, schema, table, and the set of indexed columns.