Section 3.24. <ServiceDatabase SQL>

3.24. <ServiceDatabase SQL>

This optional clause specifies an external SQL Service Database for radiusd.

ServiceDatabase SQL has a number of customisable SQL statements (AddServiceQuery, GetServiceQuery, UpdateServiceQuery, DeleteServiceQuery, AddSubscriptionQuery, GetSubscriptionQuery, UpdateSubscriptonQuery and DeleteSubscriptionQuery). These statements are used to access the entries in the SQL Service Database. The default statements will work with the example SERVICES and SUBSCRIPTIONS tables in the example SQL schema in file goodies/hotspot.sql. If you wish, you can use more or fewer columns in your SQL Service Database, and you can change the names of the columns or the table. If you do use a different table schema, you will probably have to change statements to match your schema.

You can configure the SQL database(s) that

ServiceDatabase
                  SQL

uses by defining one or more DBSource, DBUsername and DBAuth lines. For more information about SQL configuration and failure behaviour, see Section 3.8. SQL configuration.

ServiceDatabase SQL understands also the same parameters as ServiceDatabase xxxxxx. For more information, see Section 3.22. <ServiceDatabase xxxxxx>. It supports also all the common SQL configuration parameters. For more information about the SQL configuration parameters, see Section 3.8. SQL configuration.

3.24.1. SQL Bind Variables

All ServiceDatabase SQL statements support SQL bind variables. Certain exceptions are documented for each query. For more information, see Section 3.8.1. SQL bind variables. An example of DeleteSubscriptionQuery with bind variables is:

DeleteSubscriptionQuery DELETE FROM SUBSCRIPTIONS WHERE tenant_id=? AND id=?
DeleteSubscriptionQueryParam %2
DeleteSubscriptionQueryParam %3

3.24.2. AddServiceQuery

An SQL statement to add a new service. Special formatting characters may be used. Special variable %2 contains a tenant id, %3 contains object's id and %4 contains object's name. If AddServiceQuery is defined as an empty string, then the query will not be executed.

%0 and %1 are replaced with service parameter names and their values, respectively, and can not currently be used as SQL bind parameters.

AddServiceQuery defaults to:

INSERT INTO SERVICES (%0) VALUES (%1)

3.24.3. GetServiceQuery

An SQL statement to get a service. Special formatting characters may be used. Special variable %2 contains a tenant id, %3 contains object's id and %4 contains object's name. If GetServiceQuery is defined as an empty string, then the query will not be executed.

%0 and %1 are undefined.

GetServiceQuery defaults to:

SELECT * FROM SERVICES WHERE tenant_id=%2 AND (name=%4 OR id=%3)

3.24.4. UpdateServiceQuery

An SQL statement to update an existing service. Special formatting characters may be used. Special variable %2 contains a tenant id, %3 contains object's id and %4 contains object's name. If UpdateServiceQuery is defined as an empty string, then the query will not be executed.

%0 contains list of service key=value pairs and can not be currently used as a SQL bind parameter. %1 is undefined.

UpdateServiceQuery defaults to:

UPDATE SERVICES SET name=%4 WHERE tenant_id=%2 AND id=%3

3.24.5. DeleteServiceQuery

An SQL statement to delete a service. Special formatting characters may be used. Special variable %2 contains a tenant id, %3 contains object's id and %4 contains object's name. If DeleteServiceQuery is defined as an empty string, then the query will not be executed.

%0 and %1 are undefined.

DeleteServiceQuery defaults to:

DELETE FROM SERVICES WHERE tenant_id=%2 AND id=%3

3.24.6. AddSubscriptionQuery

An SQL statement to add a new subscription. Special formatting characters may be used. Special variable %2 contains a tenant id, %3 contains object's id and %4 contains object's name. If AddServiceQuery is defined as an empty string, then the query will not be executed.

%0 and %1 are replaced with subscription parameter names and their values, respectively, and can not currently be used as SQL bind parameters.

AddSubscriptionQuery defaults to:

INSERT INTO SUBSCRIPTIONS (%0) VALUES (%1)

3.24.7. GetSubscriptionQuery

An SQL statement to get a subscription. Special formatting characters may be used. Special variable %2 contains a tenant id, %3 contains object's id and %4 contains object's name. If GetSubscriptionQuery is defined as an empty string, then the query will not be executed.

%0 and %1 are undefined.

GetSubscriptionQuery defaults to:

SELECT * FROM SUBSCRIPTIONS WHERE tenant_id=%2 AND (name=%4 OR id=%3)

3.24.8. UpdateSubscriptionQuery

An SQL statement to update an existing subscription. Special formatting characters may be used. Special variable %2 contains a tenant id, %3 contains object's id and %4 contains object's name. If UpdateSubscriptionQuery is defined as an empty string, then the query will not be executed.

%0 contains list of subscription key=value pairs and can not currently be used as a SQL bind parameter. %1 is undefined.

UpdateSubscriptionQuery defaults to:

UPDATE SUBSCRIPTIONS SET %0 WHERE tenant_id=%2 AND id=%3

3.24.9. DeleteSubscriptionQuery

An SQL statement to delete a subscription. Special formatting characters may be used. Special variable %2 contains a tenant id, %3 contains object's id and %4 contains object's name. If DeleteSubscriptionQuery is defined as an empty string, then the query will not be executed.

%0 and %1 are undefined.

DeleteSubscriptionQuery defaults to:

DELETE FROM SUBSCRIPTIONS WHERE tenant_id=%2 AND id=%3