Hi, i’ve got an enviroment using custom mysql database as auth backend. This mysql db is from postifx and the fields in it are name, email, password. The relevant part from openfire.xml is:
<jdbcAuthProvider>
<passwordSQL>select password from mailbox where username = concat_ws(’@’,?,‘domain’)</passwordSQL>
<passwordType>plain</passwordType>
</jdbcAuthProvider>
<jdbcUserProvider>
<loadUserSQL>select name,username from mailbox where username = concat_ws(’@’,?,‘domain’)</loadUserSQL>
<userCountSQL>select count(*) from mailbox</userCountSQL>
<allUsersSQL>SELECT SUBSTRING_INDEX(username, ‘@’, 1) FROM mailbox</allUsersSQL>
<searchSQL>select substring_index(username,’@’,1) from mailbox where username = concat_ws(’@’,?,‘domain’)</searchSQL>
<usernameField>username</usernameField>
<nameField>name</nameField>
<emailField>username</emailField>
</jdbcUserProvider>
In the name fied i’ve got the complete name, i.e. pedro perez
in the username field i’ve got the username like pperez
My problems are
-
from spark i cannot add contacts and get their nicknames, i’m getting their usernames instead, as described in this thread http://www.igniterealtime.org/community/message/165101#165101
-
when a customer goes to the fastpath webchat, the customer sees the username agent instead of their nickname or complete name.
These are very troublesome for us because the customer doesn’t have the chance to really now to whom he’s talking at the time.
Any workaround or solution for this issue?
Thanks for your help
desc mailbox
-> ;
*----
*–
+–+-+
+-----+
Field
Type
Null
Key
Default
Extra
*----
*–
+–+-+
+-----+
username
varchar(255)
NO
PRI
NULL
password
varchar(255)
NO
NULL
name
varchar(255)
NO
NULL
maildir
varchar(255)
NO
NULL
quota
int(10)
NO
0
domain
varchar(255)
NO
NULL
created
datetime
NO
0000-00-00 00:00:00
modified
datetime
NO
0000-00-00 00:00:00
active
tinyint(1)
NO
1
*----
*–
+–+-+
+-----+
select * from mailbox where username = “pperez@domain”;
*----
*–
+
+
+–-+
+
+
+–
username
password
name
maildir
quota
domain
created
modified
active
*----
*–
+
+
+–-+
+
+
+–
pperez@domain
password
PEDRO PEREZ
domain/seaq/
0
domain
2008-04-02 09:15:58
2008-04-02 09:15:58
1
*----
*–
+
+
+–-+
+
+
+–
1 row in set (0.00 sec)