List user profile information from os_propertyentry table

Enrique Cadalso May 29, 2013

Hello,

I am trying to list usernames with profile information (Phone, department, etc.) using the SQL Plugin for Confluence. I found that information is stored in os_propertyentry table.

os_propertyentity

However I cant find the match with cwd_user table.

cwd_user table

The IDs dont match and there is no username in os_propertyentry table.

  • How the user information information in os_propertyentry table is linked with cwd_user table?
  • Is there any other table to make the linking?

Thanks

Enrique.

3 answers

1 accepted

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

2 votes
Answer accepted
Enrique Cadalso May 30, 2013

Actually the table structure in Confluence is different from JIRA. There is no os_propertystring table. In this case in Confluence the linking table can be content table. It has the fields

contentid, contenttype, title, version, creator, creationdate, lastmodifier, lastmoddate, versioncomment, prevver, content_status, spaceid, child_position, parentid, messageid, pluginkey, pluginver, draftpageid, draftspacekey, drafttype, draftpageversion, pageid, parentcommentid, username

Where the field prevver contains the id that matches with the entity_id in the records at the os_propertyentry table. So using

SELECT DISTINCT content.username, content.prevver AS id_os_propertyentry
   FROM content
  WHERE content.contenttype::text = 'USERINFO'::text AND content.prevver IS NOT NULL
  ORDER BY content.username;

We finish with a view with the username and the id in os_propertyentry to match the users with the its profile properties stored in that table.

Matt Doar
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.
May 30, 2013

Oops, didn't see that it was Confluence. Handy answer, thanks

0 votes
Matt Doar
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.
May 30, 2013

I think you mean os_propertyentry and in your case the data will be in os_propertystring. In my book Practical JIRA Plugins there's a whole chapter about how plugins use the os_property* tables. I've pasted in an excerpt that may help

~Matt

An Example of Storing Data

Understanding propertyentry is much easier with a concrete example. Let’s say I want to store two street addresses. In XML, they might look something like this:

<address id="10010" housenumber="10" street="Oak Avenue" city="San Jose"> <address id="10200" housenumber="32" street="Long Street" city="London">

In the propertyentry table, the data would look like this:

ID ENTITY_NAME

100 Address 101 Address 102 Address

103 Address 104 Address 105 Address

ENTITY_ID PROPERTY_KEY PROPERTYTYPE

10010 House_Number 5 10010 Street 5 10010 City 5

10200 House_Number 5 10200 Street 5 10200 City 5

First, the ID column is a unique identifier for every piece of data in this table.

Then, the entity name Address is the kind of data we want to store. In an Object Rela- tional Model (ORM), this is the class name of the object being stored.

Then comes the entity ID, which distinguishes multiple addresses from each other. In an ORM, this is a unique identifier for each instance of an object.

Then the property key contains the name of a data field within each address. This is the “key” part of the “key=value” pair that is being stored.

There’s one last field in the propertyentry table: propertytype. The most common value is 5, which means that the value is a string less than 255 characters long and is stored in the propertystring table. A value of 1 is for boolean settings such as enabling or disabling voting and its value is stored in the propertynumber table. A value of 6 is for larger blocks of text such as the license data and is stored in propertytext.

The values in the propertystring table for our example would be: ID PROPERTYVALUE

100 10
101 Oak Avenue 102 San Jose

103 32

104 Long Street 105 London

The ID field in this secondary table is the ID of each entry in the propertyentry table. The propertyvalue is the “value” part of the “key=value” pair that is being stored for that row in propertyentry.

Enrique Cadalso May 30, 2013

Hi Matt,

Thanks for your explanation (and for spotting the typo!). I am matching your answer with what I have in the tables.

Regards,

Enrique

Martin Cleaver
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.
May 30, 2013

Thanks for your help, Matt. I just bought your book (via Safari Books Online).

Enrique Cadalso May 30, 2013

Hi Matt,

Thanks for your help. I finally found where the missing id was. I write the solution in another comment.

Uday Kumar May 6, 2014

can you please link where you posted the comment?

0 votes
Enrique Cadalso May 29, 2013

Note: The screenshots are high resolution, opening in a new page/tab or zooming in will show the details.

TAGS
AUG Leaders

Atlassian Community Events