Database Specific Configurations
In order to use the description field in a report, Codebeamer uses full-text index. If it does not exist, users need to create it for the database configured for Codebeamer.
Oracle
About Oracle database
Execute privilege on DBMS_LOB
If the configured database is Oracle then it is necessary to grant Execute privilege on DBMS_LOB package. By default this package is readable and executable for anybody but the database administrator can revoke this privilege.
GRANT EXECUTE ON DBMS_LOB TO <database user>;
Full-text index on TASK table
 |
The full-text index setup using Oracle 19.3c is a mandatory step for a successful database scheme creation.
|
The CTXSYS.CTX_DDL is an Oracle package that is required for configuring the full-text index. If Codebeamer has no access to the package, full-text indexing cannot be performed.
To provide execute privileges on the package, run the following SQL as a System Administrator. The <database user> should be the database user configured in Codebeamer:
GRANT EXECUTE ON CTXSYS.CTX_DDL TO <database user>;
Run the following SQL (for example, in sqlplus) with the database user of Codebeamer (please notice that terminating '/' character at the end of the command):
begin
begin
CTX_DDL.DROP_STOPLIST ('TASK_DETAILS_STOPLIST');
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('NOT EXIST');
end;
CTX_DDL.CREATE_STOPLIST ('TASK_DETAILS_STOPLIST', 'BASIC_STOPLIST');
CTX_DDL.ADD_STOPWORD ('TASK_DETAILS_STOPLIST', 'a');
CTX_DDL.ADD_STOPWORD ('TASK_DETAILS_STOPLIST', 'an');
CTX_DDL.ADD_STOPWORD ('TASK_DETAILS_STOPLIST', 'no');
CTX_DDL.ADD_STOPWORD ('TASK_DETAILS_STOPLIST', 'not');
EXECUTE IMMEDIATE 'CREATE INDEX task_details_fidx ON TASK(DETAILS) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS(''SYNC(ON COMMIT) STOPLIST TASK_DETAILS_STOPLIST'')';
end;
/
begin
CTX_DDL.DROP_STOPLIST ('TASK_DETAILS_STOPLIST');
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('NOT EXIST');
end;
CTX_DDL.CREATE_STOPLIST ('TASK_DETAILS_STOPLIST', 'BASIC_STOPLIST');
CTX_DDL.ADD_STOPWORD ('TASK_DETAILS_STOPLIST', 'a');
CTX_DDL.ADD_STOPWORD ('TASK_DETAILS_STOPLIST', 'an');
CTX_DDL.ADD_STOPWORD ('TASK_DETAILS_STOPLIST', 'no');
CTX_DDL.ADD_STOPWORD ('TASK_DETAILS_STOPLIST', 'not');
EXECUTE IMMEDIATE 'CREATE INDEX task_details_fidx ON TASK(DETAILS) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS(''SYNC(ON COMMIT) STOPLIST TASK_DETAILS_STOPLIST'')';
end;
/
MySQL
Full-text index on TASK table
|
|
During the database scheme creation, the full-text index is created automatically thus the command(s) below should be executed only if the index could not be successfully generated, for example, due to missing database privileges.
|
The full-text index is available only from MySQL 5.6+. Run the following SQL with the database user of Codebeamer:
CREATE FULLTEXT INDEX task_details_fidx ON task(details);
PostgreSQL
Full-text index on TASK table
|
|
During the database scheme creation, the full-text index is created automatically thus the command(s) below should be executed only if the index could not be successfully generated, for example, due to missing database privileges or the contrib package is only installed after the Codebeamer database was already initialized.
|
This feature is optional and requires the PostgreSQL contrib extensions installed on the database server. By some distributions (for example CentOS) these reside in a separate package and need to be installed separately.
Run the following SQL with a System Administrator user, changed to the Codebeamer database as the active database:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
Run the following SQL with the database user of Codebeamer:
CREATE INDEX task_details_trgm ON task USING gin (UPPER(details) gin_trgm_ops);