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.
However I cant find the match with cwd_user table.
The IDs dont match and there is no username in os_propertyentry table.
Thanks
Enrique.
Community moderators have prevented the ability to post new answers.
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.
Oops, didn't see that it was Confluence. Handy answer, thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks for your help, Matt. I just bought your book (via Safari Books Online).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Matt,
Thanks for your help. I finally found where the missing id was. I write the solution in another comment.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Note: The screenshots are high resolution, opening in a new page/tab or zooming in will show the details.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.