rlm_sqlite and sqlite3_step(), sqlite schema

[Ben West]

Apologies for posting on the freeradius-user list about this.

I serendipitously happened across this list's sqlite-related thread,
and the related bugzilla entry, on very same day I was playing around
with sqlite on v2.1.6 myself.
https://bugs.freeradius.org/bugzilla/show_bug.cgi?id=3

An issue not addressed in the patch proposed in that bug entry is that
the sql_sqlite.c apparently only invokes sqlite3_step() in its
function sql_fetch_row(), which AFAIK is only called for SELECT
queries.  This has the effect of UPDATE, DELETE, INSERT queries being
compiled but never executed.

Below is the kludge I did to to sql_query() to get around this, but it
does not account for lower case or leading spaces in querystr.  Since
this happened in unwitting parallel, my version of sql_query() is out
of step with the patch in bugzilla.

static int sql_query(SQLSOCK * sqlsocket, SQL_CONFIG *config, char *querystr)
{
        int status;
        rlm_sql_sqlite_sock *sqlite_sock = sqlsocket->conn;
        const char *zTail;

        if (config->sqltrace)
                radlog(L_DBG,"rlm_sql_sqlite: query:  %s", querystr);
        if (sqlite_sock->pDb == NULL) {
                radlog(L_ERR, "rlm_sql_sqlite: Socket not connected");
                return SQL_DOWN;
        }

        status = sqlite3_prepare(sqlite_sock->pDb, querystr,
strlen(querystr), &sqlite_sock->pStmt, &zTail);
        radlog(L_DBG, "rlm_sql_sqlite: sqlite3_prepare() = %d\n", status);
        sqlite_sock->columnCount = 0;

        if (strstr(querystr, "SELECT") == querystr)
        {
                return (status == SQLITE_OK) ? 0 : SQL_DOWN;
        }
        /* If this is not SELECT statement, must go ahead and invoke
sqlite3_step() */
        else if (strstr(querystr, "INSERT") == querystr ||
strstr(querystr, "UPDATE") == querystr || strstr(querystr, "DELETE")
== querystr  )
        {
                status = sqlite3_step(sqlite_sock->pStmt);
                radlog(L_DBG, "rlm_sql_sqlite: sqlite3_step = %d\n", status);
                return (status == SQLITE_DONE) ? 0 : SQL_DOWN;
        }

}


I also have versions of the schema and conf files for sqlite (i.e.
raddb/sql/sqlite/*) but I wanted to ping the list first to make sure
that is not already underway with someone else.

-- 
Ben West
westbywest at gmail.com