Tom Donovan wrote:
> KaiGai Kohei wrote:
>> Tom Donovan wrote:
>>> Yes, SQL *functions* only return a single value - but if your
>>> database supports SQL *stored procedures* (like the example), they
>>> return a set of rows; including any extra values to be assigned to
>>> environment variables. For example:
>>>
>>> DROP PROCEDURE IF EXISTS digest;
>>> CREATE PROCEDURE digest(username VARCHAR(64), realm VARCHAR(64))
>>> SELECT md5(concat(uname,':', realm ,':', upass)), uctx AS
>>> CONTEXT, uexpiration AS EXPIRES
>>> FROM uaccount WHERE uname = username;
>>>
>>> When httpd executes the CALL statement from:
>>>
>>> AuthDBDUserRealmQuery "CALL digest(%s, %s)"
>>>
>>> this will authenticate the user, and if successful - it will also set
>>> the two httpd environment variables AUTHENTICATE_CONTEXT and
>>> AUTHENTICATE_EXPIRES to values from the database.
>>>
>>> Stored procedures are available in MySQL, Oracle, and several other
>>> databases - but some databases, like PostgreSQL and SQLite, do not
>>> support them.
>>
>> My target is PostgreSQL, and its function has a regional dialect. :-)
>>
>> Example)
>> apache=# CREATE OR REPLACE FUNCTION digest_f (TEXT, TEXT) RETURNS
>> RECORD LANGUAGE 'sql' AS
>> 'SELECT md5(uname || '':'' || $2 || '':'' || upass),
>> context FROM uaccount WHERE uname = $1';
>> CREATE FUNCTION
>> apache=# SELECT * FROM digest_f ('foo', 'sample realm') AS (hash
>> text, context text);
>> hash | context
>> ----------------------------------+---------
>> 29dd4bc0ed3d043849fc3efbb05876cd | *:s0:c0
>> (1 row)
>>
>> However, I would like to consider this kind of avoidance as an
>> independent
>> topic (or a workaround for a while). The fixed order parameters are
>> really
>> an limitation on writing a query for mod_authn_dbd, so it should be
>> fixed.
>>
>> Thanks,
>
> I understand why you would prefer the simpler alternative of
> AuthDBDRealmUserQuery, but I disagree with your conclusion.
>
> Your argument appears reasonable for the current set of dbd modules,
> where AuthDBDUserRealmQuery is the only query specified with multiple
> parameters - but in future Apache versions there may be more.
It seems to me you overlook my another concern that mod_authn_dbd
does not allow to use variable parameters such as remote address.
> For example: http://httpd.apache.org/docs/2.3/mod/mod_session_dbd.html
> requires five queries (deletesession, updatesession, insertsession,
> selectsession and cleansession). Three of these queries take multiple
> parameters. We probably won't want to introduce variations of these
> queries for whenever the argument order is inconvenient to a particular
> database. Ditto for other future dbd modules.
It cannot be a reason why we enforce a fixed order and fixed type
parameters for other dbd modules. It is not a complex feature to put
parameters based on extra options, so the upcoming mod_session_dbd
should also have an extra option to specify the order/type of parameters.
(Look at my patch, it is less than 200 lines.)
> This is a common problem whenever a "SQL API" is used - and the
> commonly-used solution (stored procedures, or "functions returning rows"
> in the case of PostgreSQL) seems like a better way to go than adding
> additional options to Apache whenever a configuration requires some
> extra SQL coding effort.
It is not a problem in SQL API. If we can pay a bit of efforts to
specify the order/types of parameters, this problem will go away.
I don't think we should spare our efforts and give burdens to users.
Needless to say, I can pay my efforts to solve the problem.
Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei <[email protected]>
