Set Up a SQL Backend as Fact Store
You can use a SQL database to store facts and qualifiers for your Fact Store.
Answer Server can connect to any RDBMS that supports SQL. You specify how to connect to the database by setting the appropriate connection string in the ConnectionString
configuration parameter in the FactBank configuration. The most fully tested options are:
- SQLite
- PostgreSQL (version 9.3 or later is required)
The SQL Fact Store has two required tables, one for facts, and one for qualifiers. The facts table stores the values of entity properties. The qualifiers table stores the names and values of particular sets of qualifiers associated with the properties. Each table includes qualifier combination values, which link the properties to the associated qualifiers.
In addition, there are two optional tables, one for sources and one for security types. The sources table stores information about the sources of your facts, including optional security information to allow you to restrict the facts by user permissions. The security types table stores the security configuration information for your security types.
Your Answer Server installation includes PostgreSQL schema files for these tables, and a utility script that you can use to apply these schemas to a database by using the psql utility. These files are available in the /factbank/schemas/postgresql
directory in your installation.
The following sections describe the tables in more detail, and provide some best practices for how to organize your fact stores.
Manage Your Tables
OpenText recommends that you organize your tables by creating a separate database for each set of facts and qualifiers. In this case, each database is the backend for its own Fact Bank system, which optimizes the database queries required for an Ask
action.
For example, if you have a collection of facts about company sales histories, and a collection of facts about the products that a company offers, you might create a sales
database and a products
database. Each database is a separate Fact Bank instance in your Answer Server setup, and you can easily query one or both, as required.
Facts Table
The facts table stores the values of entity properties.
This table must have the name facts
. The facts table has four required columns, and one optional column, described in the following table.
Column | Type | Description |
---|---|---|
entity_code
|
text | The code for the entity that this row is about, from your entity_to_code.txt coding file. |
property_code
|
text | The code for the property that this row is about, from your property_to_code.txt coding file. |
property_value
|
text | The value of this property for the specified entity, in the associated qualifier combination. |
qualifier_combination
|
integer | The reference value for the rows in the qualifiers table that contain qualifiers that apply to a particular property value. This value corresponds to the values in the qualifier_combination column in the qualifiers table. Answer Server uses this column to find the appropriate property, entity, or property value when a question contains a particular qualifier. If this value does not correspond to a value in the qualifiers table, Answer Server treats it as having no qualifiers. OpenText recommends that you reserve a value to use for properties that do not have qualifiers (usually 0). |
source_id
|
integer | (Optional) The reference value for the row in the sources table that contains the source for this fact. This value corresponds to the values in the id column in the sources table. Answer Server uses this column to find and return the source for a particular fact. If this value is missing, or does not correspond to a value in the sources table, Answer Server returns the source as SQLDB. OpenText recommends that you reserve a value to use for properties that do not have source information (usually 0). |
You can optimize the performance of the facts table by creating indexes on each column. For example, in a PostgreSQL instance, OpenText recommends that you create a btree index on each column.
Qualifiers Table
The qualifiers table stores the codes and values for qualifiers, and the qualifier combination reference that links a qualifier to a row in the facts
table.
The table must have the name qualifiers
. The qualifiers table has three columns, described in the following table.
Column | Type | Description |
---|---|---|
qualifier_combination
|
integer |
The qualifier combination reference that identifies qualifiers that are associated with a particular property value for an entity. |
qualifier_code
|
text | The code for the property that this qualifier is about, from your property_to_code.txt file |
qualifier_value
|
text | The value of the qualifier in this qualifier combination |
Most data sets will have multiple rows with the same qualifier combination. For example, if your facts table contains the ares of different types of land in a country over time, you might have something like the following table for qualifiers.
qualifier_combination | qualifier_code | qualifier_value |
---|---|---|
1 | LANDTYPE | Farmland |
1 | YEAR | 2015 |
2 | LANDTYPE | Forest |
2 | YEAR | 2015 |
3 | LANDTYPE | Farmland |
3 | YEAR | 2016 |
4 | LANDTYPE | Forest |
4 | YEAR | 2016 |
In this case, the qualifier combination 1 relates to farmland in 2015, qualifier combination 2 relates to forest in 2015, and so on.
You can optimize the performance of the qualifiers table by creating indexes on each column. For example, in a PostgreSQL instance, OpenText recommends that you create a btree index on each column.
Sources Table
The sources table is an optional table to allow you to store the sources for your facts. You can use this option with the source_id
column in the facts table to store the details of the fact sources. Answer Server returns the source information with the fact when it returns in an Ask
action.
The table must have the name sources
. The sources table has two required columns and two optional columns, described in the following table.
Column | Type | Description |
---|---|---|
id
|
integer | A primary key integer ID value for the source. |
source
|
text | The name of the source. Answer Server returns this value in the Ask action when it returns a fact that has the associated source ID. |
acl
|
text | (Optional) The Access Control List (ACL) for this source. You can use this option if you want to use user security for your facts. |
security_type_id
|
integer |
(Optional) The reference value for the row in the |
Security_Types Table
The security types table is an optional table to allow you to store the security type information for your sources. You can use this option with the security_type_id
column in the sources table to store the details of the security types.
The table must have the name security_types
. The security types table has two columns, described in the following table.
Column | Type | Description |
---|---|---|
id
|
integer | A primary key integer ID value for the security type. This value corresponds to the IDs that you use in the sources table. |
friendly_name
|
text | The name of the security type. This value must correspond to the security type configuration section in your Answer Server configuration file. |