#1 join between tables

[Didier B]

I remark that this module doesn't know how to handle
the following database schema which is quite often seen.

mysql> desc users;
+----------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key |
Default | Extra |
+----------------+----------------------+------+-----+---------+----------------+
| user_id | smallint(5) unsigned | | PRI |
NULL | auto_increment
| user_login | varchar(16) | YES | MUL |
NULL | |

mysql> desc users_of_groups
-> ;
+---------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key |
Default | Extra |
+---------------+----------------------+------+-----+---------+-------+
| user_id | smallint(5) unsigned | | MUL | 0
| |
| group_id | smallint(5) unsigned | | MUL | 0
| |
| user_rights | tinyint(3) unsigned | | |
32 | |
| from_group_id | varchar(255) | | |
| |
+---------------+----------------------+------+-----+---------+-------+

So i cheat to make it understand the join betwenn my
tables doing this :

AuthMySQLUserTable users,users_of_groups
AuthMySQLNameField user_login
AuthMySQLPasswordField user_password
AuthMySQLUserCondition
users_of_groups.user_id=users.user_id

AuthMySQLGroupTable users,users_of_groups
AuthMySQLGroupField group_id
AuthMySQLGroupCondition
users_of_groups.user_id=users.user_id
require group 281

to understand what does it do, simply put your mysql in
logging mode
by adding
log=mysqld.log
to your /etc/my.cnf

then tail -f your logs

you will see the queries made by the module

SELECT user_password FROM users,users_of_groups WHERE
user_login='bringer' AND
users_of_groups.user_id=users.user_id

SELECT group_id FROM users,users_of_groups WHERE
user_login='bringer' AND
users_of_groups.user_id=users.user_id

make the simplier would be to be able to pass the exact
sql query to the module

[Jerry Stuckle]

Logged In: YES
user_id=1049703

You can also place your user and group conditions in double
quotes ("...") and thereby include spaces in the condition.

I'm looking into specifying the exact SELECT statements. I
need to see how it will affect the rest of the module.

[Jerry Stuckle]

Logged In: YES
user_id=1049703

mod_auth_mysql supports joining of multiple tables and
multiple conditions in the WHERE clause. You can even add
spaces to the AuthMySQLUserTable, AuthMySQLUserCondition,
etc. parameters by enclosing the line in double quotes (" ").

Do you still see a need for passing the SELECT statements to
the module?

Thanks,

Jerry

[Didier B]

Logged In: YES
user_id=527345

Hello

OK I didn't understand at first that we could use double quotes

But I still think that it could be simplier if there were a
AuthMySQLQuery parameter that would exclude all the others
AuthMySQLxxx

For someone that known SQL it could lead to very nice and
complex queries... (why not left join, or special name of
fields )

Bye

[Jerry Stuckle]

Logged In: YES
user_id=1049703

Didier,

OK, let me think about it some.

I'm still not sure it's a good idea. This one has the
possibility of adding more complications to the code, and
I'm not sure this much flexibility is really necessary. And
it would both user and group authorization - and we're
already having some problems in this area...

Do you have a real-life example where you would need to do a
more complicated query than you can currently do?

Jerry