what database table keep user properties?

[Aspect Infra Team]

does somebody know where in database JIRA keeps this user-properties?

[Henning Tietgens · Accepted solution]

It's a combination of

external_entities on (user_name = external_entities.name)

propertyentry on (external_entities.id = propertyentry .entity_id)

propertystring on (propertyentry .id = propertystring .id)

WHERE

propertyentry.entity_name = 'ExternalEntity'

propertyentry.property_key = 'jira.meta.xxx'

xxx is the property name and propertystring.propertyvalue is the value

Henning

[Aspect Infra Team]

thanks Henning

[Gregory Kneller]

It looks like propertyentry.entity_id does not relate to external_entities.id any more, does it?

[Gregory Kneller]

Well, in JIRA 6 one shall use app_user instead of external_entities

[Gregory Kneller]

For JIRA 6, it is changed

SELECT
e.user_key,
t.property_key,
s.propertyvalue as FieldValue
FROM
app_user e 
JOIN propertyentry t
ON
e.ID = t.entity_id
JOIN propertystring s
ON
t.ID = s.ID
where t.property_key like 'jira.meta%';

[Pavel Bambas]

Thank you very much :)

[Joseph Than]

Thank you for this.

[Sameera Shaakunthala [inactive]]

SQL:

select
  ps.propertyvalue
from
  propertystring ps,
  external_entities ee,
  propertyentry pe
where
  ee.name(+) = '<USERNAME HERE>' and
  pe.property_key(+) = 'jira.meta.' || '<PROPERTY KEY HERE>' and
  ee.entitytype(+) = 'com.atlassian.jira.user.OfbizExternalEntityStore' and
  ee.id = pe.entity_id(+) and
  pe.id = ps.id(+);

[Onkar Ahire]

You can retrive property by programatically in future, you can use below code.

PropertySet properties;

properties=userPropertyManager.getPropertySet("USERNAME");

String x=properties.getString("jira.meta.x");