freeradius-server
freeradius-server copied to clipboard
FreeRADIUS
Prepared queries in SQL.
This document refers to PostgreSQL, but other databases should be similar. These notes are rough, but the basic idea is there.
Executing prepared queries: http://www.postgresql.org/docs/9.3/static/libpq-exec.html
Creating prepared queries (syntax): http://www.postgresql.org/docs/9.2/static/sql-prepare.html
Goal
To have SQL sub-modules automatically create prepared queries and use them.
The queries are per connection, so we update:
rlm_sql.h
typedef struct rlm_sql_handle {
void *conn; //!< Database specific connection handle.
rlm_sql_row_t row; //!< Row data from the last query.
rlm_sql_t *inst; //!< The rlm_sql instance this connection belongs to.
rbtree_t *prepared; //!< prepared queries
} rlm_sql_handle_t;
prepared is handled completely by the SQL sub-module.
The source of the queries (config, xlat, etc.) needs to be used as a key for the rbtree. The contents of the tree are DB-specific prepared queries.
rlm_sql_module_tneeds to change:
sql_rcode_t (*sql_query)(rlm_sql_handle_t *handle, rlm_sql_config_t *config, void const *key, char const *query);
sql_rcode_t (*sql_select_query)(rlm_sql_handle_t *handle, rlm_sql_config_t *config, void const *key, char const *query);
where we add key. This is the CONF_PAIR, etc.
we should add a mutex to the rlm_sql struct, and a hash table which is a list of queries -> xlat, statement mappings.
The code compiling the prepared statement can look up the xlat, instead of re-doing it itself.
If we want to avoid mutexes, the server core can pre-compile all of the queries, via a new sub-module API.
except it has to do that in pass2.
the SQL submodules should have a prepare function, which gets passed a query, and which caches the prepared query.
Then in the module sql_query, we do:
- look up key in connection to see if the statement has been prepared get name, ptr to prepared run it
( if not, look up key in SQL config to get prepared statement prepare it cache it in the connection
foo = rbtree_find(config->prepared, key);
if (!foo) {
// prepare query.
Gresult *PQprepare(PGconn *conn,
const char *stmtName,
const char *query,
int nParams,
const Oid *paramTypes); // NULL, PQ will assume text and parse
// SELECT * FROM mytable WHERE x = $1::bigint;
// if we can prepare it, cache the query
}
if (foo) {
// convert xlat_exp_t to postgresql stuff????
PGresult *PQexecPrepared(PGconn *conn,
const char *stmtName,
int nParams,
const char * const *paramValues, // string or binary
const int *paramLengths, //
const int *paramFormats, // 0 for text, 1 for binary
0); // result is all text
} else {
conn->result = PQexec(conn->db, query);
}
typedef struct pg_prepared_t {
void const *key;
char const *name; // "statement-%p", key
xlat_exp_t *xlat; // needed for xlat2tmpl_array()
int nParams; // to PQexecPrepared
value_pair_tmpl_t params[1]; // array of parameters
}
for now, just pass them all as strings. It's not perfect, but it's much easier.
At run time, we print all templates to strings, and allocate an array. then pass that
to the PQexecPrepared function.
xlat
we also need an xlat2tmpl function. Which returns a template for one xlat node.
i.e. it doesn't recurse down them.
we also need a function to walk a converted XLAT struct with a context, so that we can do a "CREATE PREPARED" string.
-
don't recurse into sub-xlats
- literals go in as-is
- everything else gets replaced with $1, $2, etc.
- and converted to a template.
-
really just one xlat call to do this:
xlat2tmpl_array(xlat_exp_t _xlat, void *ctx, void (_callback)(void *ctx, value_pair_tmpl_t *tmpl));
Hi guys In our own build of freeradius we use custom rlm_sql_oracle to run queries with binds, not literals. This causes the backend DB to run quicker and more efficient. The idea is:
- parse query
- do binds
- execute
how about to do that oficially?
