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, DB2, Teradata: Limited or no native support

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);

// Create full-text index on content column
FullTextIndex contentIndex = articlesTable.AddFullTextIndex(
    "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);

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

manager.BuildSchema(schema);

// This allows searching across all three columns simultaneously

Searching with full-text index (database-specific):

// SQL Server - Using CONTAINS
Statement searchCondition = new Statement(
    "CONTAINS(content, 'database OR performance')"
);

var query = new Query()
    .Select([ articlesTable["id"], articlesTable["title"] ])
    .From(articlesTable)
    .Where(searchCondition);

using (ResultSet results = manager.Retrieve(query))
{
    // process results
}

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);

// Index title, body, and tags for comprehensive search
FullTextIndex postSearchIndex = postsTable.AddFullTextIndex(
    "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.