Help Center

Menu

db.update_table_name

Background

This parameter specifies the name of an 'update event table' which is populated with records indicating specific database records which have been added, updated or removed. Events should be added to this table as they occur, and are removed as they are processed by Funnelback.

The update table should use the following schema (with appropriate column types for your database system).

CREATE TABLE updateTable
  (
    id integer PRIMARY KEY AUTOINCREMENT,
    event_type varchar,
    item_key varchar,
    timestamp timestamp DEFAULT CURRENT_TIMESTAMP
  );

The id column is used only to uniquely identify records (so they can be deleted after processing).

The event_type column should contain one of the following string values indicating the type of event which occurred:

  • add

  • update

  • delete

The item_key column should contain the primary key of the added/updated/deleted record (See the primary id column and single item sql settings).

The timestamp column is not currently used, but is useful for auditing purposes.

Populating the event table

The update table may be populated either by some application with knowledge about changes to the database, or by adding database triggers to the appropriate tables. The following example provides an outline of the required configuration for the second approach.

Please note that the syntax of triggers varies from database to database. The example presented is for the SQLite database system. Please consult your database system’s documentation for the required syntax.

Create the underlying table.

CREATE TABLE if not exists main (id varchar, content varchar);

Create a table to hold the update events.

CREATE TABLE if not exists updateTable
    (id integer PRIMARY KEY AUTOINCREMENT, event_type varchar,
    item_key varchar, timestamp timestamp DEFAULT CURRENT_TIMESTAMP);

Create a trigger on the underlying table, such that when a new row is inserted, an 'add' event is added to the updateTable.

CREATE TRIGGER insert_update_table INSERT ON main
    BEGIN insert into updateTable (event_type, item_key) values (\"add\", new.id); END;

Create a trigger on the underlying table, such that when a row is updated, an 'update' event is added to the updateTable.

CREATE TRIGGER update_update_table UPDATE ON main
    BEGIN insert into updateTable (event_type, item_key) values (\"update\", new.id); END;

Create a trigger on the underlying table, such that when a row is deleted, a 'delete' event is added to the updateTable.

CREATE TRIGGER delete_update_table DELETE ON main
    BEGIN insert into updateTable (event_type, item_key) values (\"delete\", new.id); END;

After completing these steps, making any change to the main table will result in a new row being added to the updateTable, which Funnelback can subsequently process.

Setting the key

Set this configuration key in the search package or data source configuration.

Use the configuration key editor to add or edit the db.update_table_name key, and set the value. This can be set to any valid String value.

Default value

(none)

Examples

db.update_table_name=updateTable

See also