The InspIRCd SQL APIv2
From the makers of InspIRCd.
This page documents the SQL API in the 1.1 series of InspIRCd.
Introduction
InspIRCd does not directly support SQL in its core. This is done for several reasons, the most important being:
- Directly supporting SQL in the core can add a dependency to InspIRCd itself, which is undesirable
- Directly supporting SQL in the core slows down the core
- It would mean it cannot be removed once compiled in, without another recompile
Therefore, in InspIRCd, SQL support is provided through a set of 'service provider' modules which abstract the database backend from the 'client' module and provide a standard interface. Features of this API include:
- Asyncronous command processing - no blocking the entire IRCd waiting for queries
- Abstraction of SQL login credentials from modules
- Abstraction of DBMS from modules
The full SQL APIv2 implementation will (grudgingly) include facilities for blocking (syncronous) queries via a different API. This is not currently implemented.
Using the APIs
Asyncronous API
This API is based around two derivatives of the Request class:
- The first is sent from the client module to the service provider containing the query and the database ID to be used.
- The second Request is sent from the service provider to the client module when the query has either succeeded or failed, and contains the whole dataset requested by the query, and information and metadata related to this dataset, such as the number of rows, columns and field names.
The client module should use the Server::FindFeature() function to obtain a pointer to the service provider module.
All of the required definitions for the SQL API can be found in the header "m_sqlv2.h".
SQLrequest - The Query
This class does very little, it simply takes a query string and a list of parameters to be escaped and inserted into the query, you then call the SQLrequest::Send() method and the query is handed off to the service provider module, after this has happened the SQLrequest may be - and should be - destroyed. When the service provider recieves the query request it assigns the query a unique ID (an unsigned long integer), this is set in SQLrequest::id and should be retrieved if neccessary before the SQLrequest object is destroyed. You may optionally set a query to be 'priority' which will give it precedence over non-priority queries queued in the service provider module.
SQLrequest is a subclass of Request and inherits Send() from Request.
An example usage of SQLrequest to dispatch a query would be:
Module* target = Srv->FindFeature("SQL");
if(target)
{
SQLrequest foo = SQLreq(this, target, "databaseid", "SELECT (foo, bar) FROM rawr WHERE foo = '?' AND bar = ?", "Hello", "42");
if(foo.Send())
{
log(DEBUG, "Sent query, got given ID %lu", foo.id);
}
else
{
log(DEBUG, "SQLrequest failed: %s", foo.error.Str());
}
}
When a query result or failure is obtained then the calling module will get a request from the service provider in it's OnRequest() method.
A few things to note about this:
- The first parameter to the SQLrequest must be a pointer to the calling Module, in any method of the class this is just 'this' as above.
- Read the following documention of the SQLrequest constructor carefully, things are not quite as they seem and you must be careful...
The memberss of SQLrequest:
unsigned long id
The unique ID which will be assigned to the query by the service provider when you call Send().
SQLerror error
If there is an error which can be detected immediately then this will contain it. There is no garauntee errors will be picked up at the SQLrequest stage.
SQLrequest(Module* source, Module* destination, const std::string &databaseid, const SQLquery &query)
- source - The 'this' pointer from the calling (client) module, where the result is returned to.
- destination - A pointer to the SQL service provider, should be obtained with Server::FindFeature("SQL")
- databaseid - Guess? :p The ID string of the database connection you want to use, typically obtained from module configuration
- query - Ok, this is where it gets complicated..so complicated it gets it's whole own section:
Query Parameters
C++ has no native type-safe way of having a variable number of arguments to a function, the workaround for this isn't easy to describe simply, but in a nutshell what's really happening when - from the above example - you do this:
SQLrequest foo = SQLreq(this, target, "databaseid", "SELECT (foo, bar) FROM rawr WHERE foo = '?' AND bar = ?", "Hello", "42");
what's actually happening is functionally this:
SQLrequest foo = SQLreq(this, target, "databaseid", query("SELECT (foo, bar) FROM rawr WHERE foo = '?' AND bar = ?").addparam("Hello").addparam("42"));
with 'query()' returning a reference to an object with a 'addparam()' member function which in turn returns a reference to that object. There are actually four ways you can create a SQLrequest..all have their disadvantages and advantages. In the real implementations the 'query()' function is replaced by the constructor of another class 'SQLquery' which holds the query string and a ParamL (std::deque<std::string>) of query parameters.
operator,()
This is essentially the same as the above example except 'addparam()' is replaced by operator,(). The full syntax for this method is:
SQLrequest foo = SQLrequest(this, target, "databaseid", (SQLquery("SELECT.. ?"), parameter, parameter));
The additional parentheses around the 4th parameter and onwards are required as operator, has low precedence and otherwise the 5th and 6th parameters would be interpreted as SQLrequest() constructor arguments - resulting in an error.
operator%()
Identical to the operator,() method except as % has higher precedence than , the extra parentheses are unneeded. The full syntax for this method is:
SQLrequest foo = SQLrequest(this, target, "databaseid", SQLquery("SELECT.. ?") % parameter % parameter);
operator,() with SQLreq #define
Building on top of the operator,() method described on front there is a preprocessor macro defined in order to make the syntax simpler, for preprocessor buffs out there the macro is defined as:
#define SQLreq(a, b, c, d, e...) SQLrequest(a, b, c, (SQLquery(d), ##e))
This basically handles all the extra parentheses and the explict SQLquery() constructor, however it is not perfect. The C/C++ preprocessor is not intelligent and cannot handle everything. For this method you must use the syntax used in the examples throughout here:
SQLrequest foo = SQLreq(this, target, "databaseid", "SELECT.. ?", parameter, parameter);
the alternative syntax:
SQLrequest foo(this, target, "databaseid", "SELECT.. ?", parameter, parameter);
will not work, the macro will not even be called and you will get compile errors. This alternative syntax will work with all the other three methods, eg:
SQLrequest foo(this, target, "databaseid", SQLquery("SELECT.. ?") % parameter % parameter);
Explicit SQLquery creation
The sixth and final method is the most hands-on, you can create an SQLquery object with it's alternative constructor:
SQLquery(const std::string &query, const ParamL ¶ms)
And pass that object into the SQLrequest constructor yourself. This is unlikely to be used much.
Using Query Parameters
So now we've seen how we can pass in multiple parameters to the SQLrequest, how about why.
To avoid SQL injection attacks all data must be 'escaped' before it is inserted into the query string, the escaping must be done by the service provider so it can be done by the DBMS-specific client library. There are several ways this could be done. One would be to have another type of PleaseEscapeMyString request to the service provider module, which would work simply but it ugly and has relatively high overhead. Instead the SQL API passes the query to the service provider in two parts:
- A query string with placeholders for the data which needs escaping and insertion
- A list of data strings to be escaped and inserted into the query string
The placeholder character used in the query string is '?', all instances of will be replaced with a parameter from the list until there are no more placeholders or no more parameters.
An example of this would be:
SQLrequest req = SQLreq(this, target, id, "SELECT foo, bar FROM rawr WHERE foo = '?' AND bar = '?'", foovalue, barvalue);
void Priority(bool priority = true)
- priority - If the query should be given priority in the query queues, the constructor makes SQLrequests low-priority by default, call this with 'true' (the default) to make the query high priority. This must be done before you call SQLquery::Send();
void SetSource(Module* mod)
- mod - Sets the source Module* of the request, this is used internally by the service provider module.
SQLresult - The Result
This is the class results of queries are returned in, it contains information about the original query and methods to access the result data, it is a subclass of Request and will be received in the client module's OnRequest() method as a Request*, it must then be cast (parent-child cast, the only acceptable kind) to a SQLresult* before it can be used. In order of decreasing preference you should use dynamic_cast<>, static_cast<> or a C-style cast.
The members of SQLresult are:
std::string query
The query string, with parameters substituted in.
std::string dbid
The database ID the query was executed on.
SQLerror error
Error value if the query failed, NO_ERROR otherwise. See below.
virtual int Rows()
Returns the number of rows in the result.
virtual int Cols()
Return the number of columns in the result
virtual std::string ColName(int column)
- column - The column index number being looked up
Get the string name of the column refered to by the given index number.
virtual int ColNum(const std::string &column)
- column - The column name being looked up
This does the reverse of ColName(), looking up a column index number from the given name.
An exception of type SQLbadColName (ModuleException -> SQLexception -> SQLbadColName) will be thrown if the name given is invalid.
virtual SQLfield GetValue(int row, int column)
- row - The index number of the row you want to obtain a value from
- column - The index number of the column you want to obtain a value from
Returns a SQLfield object, described below, for the given row/column.
virtual SQLfieldList& GetRow()
Return a list of values in a row, this should increment an internal counter so you can repeatedly call it until it returns an empty vector. This returns a reference to an internal object, the same object is used for all calls to this function and therefore the return value is only valid until you call this function again. It is also invalid if the SQLresult object is destroyed.
virtual SQLfieldMap& GetRowMap()
As above, but return a map indexed by key name.
virtual SQLfieldList* GetRowPtr()
Like GetRow(), but returns a pointer to a dynamically allocated object which must be explicitly freed. For portability reasons this must be freed with SQLresult::Free()
virtual SQLfieldMap* GetRowMapPtr()
As above, but return a map indexed by key name.
virtual void Free(SQLfieldMap* fm)
Overloaded function for freeing the lists and maps returned above.
virtual void Free(SQLfieldList* fl)
Overloaded function for freeing the lists and maps returned above.
SQLfield - A result field
One of these objects represents a single field in a table.
std::string d
The actual data in the field, in string form.
bool null
If the field was NULL in SQL terms, true if it is, false otherwise.
SQLerror - When it all goes wrong
This class is used throughout the API - in SQLrequest and SQLresult - to store error state (or lack of it), it has methods for machine-friendly error IDs as well as human-readable error strings.
SQLerror(SQLerrorNum i = NO_ERROR, const std::string &s = "")
- i - The error ID you wish to set
- s - And an error string to go with it, some error IDs have strings associated with them already.
The only constructor, the SQLerrorNum being set to NO_ERROR means, as you can guess, that there is currently no error state.
SQLerrorNum Id()
Return the current error ID.
SQLerrorNum Id(SQLerrorNum i)
- i - The new error ID
Set the error ID, returns the new value.
void Str(const std::string &s)
- s - The new error string
Set the error string.
const char* Str()
If the error string is explicitly set, returns that. Otherwise attempts to lookup an error string for the error code.
An example of the asyncronous API
This example module demonstrates how to send a query to the SQL API, with parameters for the query, and then how to intercept the reply at a later time and read information from the dataset.
#include <string>
#include "users.h"
#include "channels.h"
#include "modules.h"
#include "helperfuncs.h"
#include "inspircd.h"
#include "configreader.h"
#include "m_sqlv2.h"
class ModuleTestClient : public Module
{
private:
public:
ModuleTestClient(InspIRCd* Me)
: Module::Module(Me)
{
}
void Implements(char* List)
{
List[I_OnRequest] = List[I_OnBackgroundTimer] = 1;
}
virtual Version GetVersion()
{
return Version(1, 0, 0, 0, VF_VENDOR);
}
virtual void OnBackgroundTimer(time_t foo)
{
Module* target = ServerInstance->FindFeature("SQL");
if(target)
{
SQLrequest foo = SQLreq(this, target, "foo", "SELECT foo, bar FROM ?", "rawr");
if(foo.Send())
{
ServerInstance->Log(DEBUG, "Sent query, got given ID %lu", foo.id);
}
else
{
ServerInstance->Log(DEBUG, "SQLrequest failed: %s", foo.error.Str());
}
}
}
virtual char* OnRequest(Request* request)
{
if(strcmp(SQLRESID, request->GetId()) == 0)
{
log(DEBUG, "Got SQL result (%s)", request->GetId());
SQLresult* res = (SQLresult*)request;
if (res->error.Id() == NO_ERROR)
{
ServerInstance->Log(DEBUG, "Got result with %d rows and %d columns", res->Rows(), res->Cols());
for (int r = 0; r < res->Rows(); r++)
{
ServerInstance->Log(DEBUG, "Row %d:", r);
for(int i = 0; i < res->Cols(); i++)
{
ServerInstance->Log(DEBUG, "\t[%s]: %s", res->ColName(i).c_str(), res->GetValue(r, i).d.c_str());
}
}
}
else
{
ServerInstance->Log(DEBUG, "SQLrequest failed: %s", res->error.Str());
}
return SQLSUCCESS;
}
ServerInstance->Log(DEBUG, "Got unsupported API version string: %s", request->GetId());
return NULL;
}
virtual ~ModuleTestClient()
{
}
};
class ModuleTestClientFactory : public ModuleFactory
{
public:
ModuleTestClientFactory()
{
}
~ModuleTestClientFactory()
{
}
virtual Module * CreateModule(InspIRCd* Me)
{
return new ModuleTestClient(Me);
}
};
extern "C" void * init_module( void )
{
return new ModuleTestClientFactory;
}
