6.4 Mysql

The mysql module provides interface to MySQL database management system. It may be used to build smap databases over SQL ones.

The SQL database to use may be configured either globally, when loading the module, or locally, when defining a smap database. If a database definition lacks SQL configuration statements, then it attempts to use a globally defined connection.

Each database is configured with a SQL query template, and a set of smap reply templates to use. When dispatched a sockmap query, the database expands the SQL query template using the actual values of ‘${map}’ (the map name) and ‘${key}’ (the key value) and sends the expanded query to the | MySQL server. If the server responds with a non-empty set of tuples, the positive reply template is expanded and the result is used as a response. Otherwise, if the query produced an empty set, the smap database uses the negative reply template to create the response.

6.4.1 MySQL Configuration

The SQL database is configured using the following options:

config-file=file

Set the name of the MySQL configuration file to read. By default ‘/etc/my.cnf’ is used.

config-group=name

Set the name of the group in the MySQL configuration file, from where to read the configuration options.

The statements above allow to keep all security-sensitive information, such as MySQL username and password, in an external configuration file and thus to relax permission requirements for ‘smapd.conf’. For a detailed description of the format of such external configuration file (or option file in ‘MySQL’ parlance), see MySQL option files.

In case the use of option files is not feasible for some reason, you may specify MySQL connection and database parameters in ‘smapd.conf’ when loading the mysql module or defining a smap database. The following options are used to define MySQL connection parameters:

host=hostname

Sets the hostname or IP address of the host running the MySQL server.

port=n

Sets port number the MySQL server is listening on. Default is 3306.

socket=file

Sets the socket name, if the server is listening on a UNIX socket.

ssl-ca=file

Sets the pathname to the certificate authority file, if you wish to use a secure connection to the server via SSL.

Notice, that either ‘host’ and, optionally, ‘port’ or ‘socket’ must be used. Specifying both is senseless.

MySQL database and user credentials are defined using the following options:

database=name

Sets the name of the MySQL database to use.

user=name

Sets MySQL user name.

password=string

Sets the password for accessing the MySQL database.

When using these options, it is reasonable to tighten the permissions on ‘smapd.conf’ so that no third person could see the MySQL password. The recommended permissions are ‘0600’.

6.4.2 MySQL Query and SMAP Replies

MySQL query is defined using the following option:

query=template

Define MySQL query template.

The template may reference the following variables:

Variable Meaning
map Name of the map being queried
key Lookup key

Table 6.1: MySQL query template variables

For example:

 
database alias mysql \
  query="SELECT alias FROM aliases WHERE email='$key'"

If the database definition lacks the query option, it will attempt to use one from the module statement. If the module statement lacked it as well, an error is reported.

Reply templates define the responses to be given. They are given by the following options:

positive-reply=template

Defines a reply to be sent if the query returned a non-empty set of tuples. In addition to the variables described above (see mysql-query-vars), the template may also refer to the MySQL result columns, by using their names from the ‘SELECT’ part of the query. For example:

 
database alias mysql \
  query="SELECT alias FROM aliases WHERE email='$key'" \
  positive-reply="OK $alias"

The default positive-reply is ‘OK’.

negative-reply=template

Defines a reply to be sent if the query returned an empty set of tuples. The template may refer to the variables described in mysql-query-vars.

Default value is ‘NOTFOUND’.

onerror-reply=template

Defines a reply to be sent if an error occurred when executing the query. The template may refer to the variables described in mysql-query-vars.

Default value is ‘NOTFOUND’.