Setup full-text search (How To)

From GeoMedia Smart Client
Jump to: navigation, search





Oracle

User/Rights


GRANT CTXAPP TO [MYUSER];
 
GRANT EXECUTE ON CTXSYS.CTX_CLS TO [MYUSER];
GRANT EXECUTE ON CTXSYS.CTX_DDL TO [MYUSER]; 
GRANT EXECUTE ON CTXSYS.CTX_DOC TO [MYUSER]; 
GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO [MYUSER]; 
GRANT EXECUTE ON CTXSYS.CTX_QUERY TO [MYUSER]; 
GRANT EXECUTE ON CTXSYS.CTX_REPORT TO [MYUSER]; 
GRANT EXECUTE ON CTXSYS.CTX_THES TO [MYUSER]; 
GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO [MYUSER];



test To the top Pfeil new.png

Index

Blue.pngA Custom Lexer for case insensitivity MUST always be created


Case Insensitive - Custom Lexer


--Creation of preference for custom LEXER
BEGIN
  ctx_ddl.create_preference(
    preference_name => 'MCNO_LEXER',
    object_name     => 'BASIC_LEXER'
  );
 
  -- turn off MIXED_CASE
  ctx_ddl.set_attribute(
    preference_name => 'MCNO_LEXER',
    attribute_name  => 'MIXED_CASE',
    attribute_value => 'NO'
  );
END;
 
--Creation of text index with sync and custom lexer parameter
CREATE INDEX [IndexName]
ON [TABLE](SearchColumn)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('DATASTORE [PreferenceName] SYNC ( ON COMMIT ) LEXER MCNO_LEXER');



Blue.pngOne of the following text index should be created. So either Single Column or Multi column. NOT both of them is recommended.




test To the top Pfeil new.png

Single Column


--Simple Oracle text index 
CREATE INDEX [IndexName] ON [TABLE]([COLUMN])
INDEXTYPE IS CTXSYS.CONTEXT 
PARAMETERS ('DATASTORE CTXSYS.DEFAULT_DATASTORE');
 
--Oracle text index with sync parameter 
CREATE INDEX [IndexName] ON [TABLE]([COLUMN])
INDEXTYPE IS CTXSYS.CONTEXT 
PARAMETERS ('DATASTORE CTXSYS.DEFAULT_DATASTORE SYNC ( ON COMMIT)');



test To the top Pfeil new.png

Multi Column

Blue.pngA dummy column (for example SearchColumn (varchar2(1)) is required.


--Creation of preference of custom MULTI_COLUMN_DATASTORE
BEGIN
ctx_ddl.create_preference('[PreferenceName]', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('[PreferenceName]', 'COLUMNS', '[COLUMN1],[COLUMN2],...');
END;
 
--Creation of text index with sync parameter
CREATE INDEX [IndexName]
ON [TABLE](SearchColumn)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('DATASTORE [PreferenceName] SYNC ( ON COMMIT )');



For more information about full-text search in oracle have a look at:



test To the top Pfeil new.png

SQL Server

Configuration

  • Install full-text search with the SQL Server setup.

Fulltext sql.png

  • Create full-text index with Management Studio.

Index sql1.png

Blue.pngThe rest is pretty straightforward concerning the definition of full-text search in SQL Server.


For more information about full-text search in SQL Server have a look at: