what database table keep user properties?

Aspect Infra Team
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 25, 2013

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

4 answers

1 accepted

4 votes
Answer accepted
Henning Tietgens
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 25, 2013

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
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 25, 2013

thanks Henning

Gregory Kneller
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 20, 2015

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

Gregory Kneller
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 20, 2015

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

6 votes
Gregory Kneller
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 20, 2015

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 April 20, 2015

Thank you very much :)

Like Gregory Kneller likes this
Joseph Than December 14, 2015

Thank you for this.

Like Gregory Kneller likes this
0 votes
Sameera Shaakunthala [inactive]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 28, 2013

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(+);

0 votes
Onkar Ahire
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 25, 2013

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");

Suggest an answer

Log in or Sign up to answer