Storing Configuration in SQL Database

There is one more configuration storage implemented right now. It allows you to store the server settings in the SQL database. In most cases this is not quite useful, just opposite, very inconvenient. However. there is at least one case where you really want to keep the server configuration in the SQL database. This is in the cluster mode. If you have a Tigase cluster system of 10 or more nodes it is much easier to keep the configuration in a single central location and manage it from there, rather then go to every single machine every time you want to change some settings. You can even change any settings for all cluster nodes with a single database query.

You set the SQL storage the same way as you set it for XML file. However, there is one more parameter as you have to provide also database connection string for the server so it knows where to connect to for the settings:

  1. Parameters in init.properties file:

    --tigase-config-repo-class=tigase.conf.ConfigSQLRepository
    --tigase-config-repo-uri=connection-uri
  2. Alternatively you can provide system properties to the JVM:

    -Dtigase-config-repo-class=tigase.conf.ConfigSQLRepository
    -Dtigase-config-repo-uri=connection-uri

Please note, the current implementation for the SQL storage automatically creates the tables necessary to operate if it does not exist. So you don’t have to worry about the schema, but you should make sure that the database user used by the Tigase has permissions to create a table.

Configuration is stored in table with following schema:

create table tigase_configuration (
-- The component name by which the configuration parameter
-- is used.
  component_name varchar(127) NOT NULL,

-- The configuration property key name or identifier.
  key_name varchar(127) NOT NULL,

-- The configuration property value
  value varchar(8191) NOT NULL,

-- The cluster node by which the configuration property is read,
-- if empty it will be read by all cluster nodes.
  cluster_node varchar(255) NOT NULL DEFAULT '',

-- Additional, secondary identifier for the configuration property.
-- The configuration can be organized in a hierarchical way to allow
-- multiple occurrences of the same property name for a single
-- component, for example you can have the same property for
-- different tcp/ip ports set to a different value:
-- c2s/5222/port_type=plain
-- c2s/5223/port_type=ssl
-- the port number is a secondary identifier.
  key_node varchar(127) NOT NULL DEFAULT '',

--  Not currently used. In future it will be used to distinguish between
-- different kind of properties (initial settings, defaults, updated by
-- user, etc...)
  flag varchar(32) NOT NULL DEFAULT 'DEFAULT',

-- The system detects basic Java types and stores information about
-- the property type, when the property is read the original property
-- type is restored and provided to the component without need for
-- a parsing or conversion.
  value_type varchar(8) NOT NULL DEFAULT 'S',

-- It is not currently used. In the future it will be used to reload
-- settings changed in last, defined period of time. Basically, the
-- system can automatically check the configuration database to
-- see whether some properties have been updated, then reload
-- them and apply automatically.
  last_update           timestamp,

primary key(cluster_node, component_name, key_node,
                  key_node, flag));