How is user mapped to the avatar

Question

As an administrator of our jira installation i wan't to manage the avatars for our employees.

Is there a way to edit the content of the sql database so i can maintain the avatars?

I see the table 'avatar' and there is a filename mapped to the folder '..\Application Data\JIRA\data\avatars\'.

But how is the record in this avatar table related to the user in table 'cwd_user'? I do not see any id that is the reference. Or is it the avatar.owner column that reference the cwd_user.user_name column?

Than the question is, is Jira using the latest Avatar record? Because i see mulitple records in avatar table for one user.

And can i just add a record in the avatar table with some id? Or do i have to use a particular id?

unknown · Answer

thank you Andrzej

Andrzej Warycha1 · Answer

As for JIRA 6.3.* there are 4 tables: app_user, propertyentry, propertynumber, avatar

To display current avatars I use the following SQL query:

SELECT u.lower_user_name,a.*
  FROM app_user as u left join propertyentry as pe 
	on pe.entity_id=u.id
		left join propertynumber as pn on pe.id=pn.id
			left join avatar as a on a.id=pn.propertyvalue
  where 
	entity_name = 'ApplicationUser'  and property_key='user.avatar.id';

Janet Albion · Answer

There are 4 tables involved here:

* avatar stored the avatar list
* propertyentry stored the association between user and the avatar. using "property_key=user.avatar.id" to search for the mapping
* propertynumber stored the current avatar used
* external_entities stored the username