How to read the propertyentry database table in Jira
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Fisheye および Crucible は除く
要約
The propertyentry database table stores a variety of data in Jira, from Issue Properties crucial to some features and apps (like Roadmaps Parent Link and Plans exclusion data) to system configs and app or plugin management.
This article aims at helping to read that table and join each property to the other tables where the actual property values are stored.
You may find the developer-oriented documentation useful, too:
環境
Any version of Jira Software or Jira Service Management, both Data Center and Server.
ソリューション
Entity type and Property key distribution
You may learn the distribution of each property type and key through this query:
select
entity_name,
property_key,
count(id)
from
entity_property
/* where entity_name = 'IssueProperty' */ /* OPTIONAL FILTER */
group by
entity_name,
property_key
order by
3 desc, 2 asc;It should output something like:
ENTITY_NAME | PROPERTY_KEY | COUNT(ID)
---------------+---------------------------------------------+-----------
IssueProperty | sd.initial.field.set | 2919377
IssueProperty | jpo-issue-properties | 1680111
IssueProperty | jpo-exclude-from-plan | 199209
IssueProperty | request.channel.type | 70771
IssueProperty | request.public-activity.lastupdated | 60789
IssueProperty | feedback.token.key | 36503
IssueProperty | service-request-feedback-comment | 1761
IssueProperty | sd.kb.shared | 510
IssueProperty | codebarrel.automation.comment.action.6900 | 502Corresponding property values
To obtain the respective property values, we need to make left joins to 6 other possible tables based on the value in the propertyentry.propertytype:
| Value in propertytype | Table to join |
|---|---|
| 1,2, 3 | propertynumber |
| 4 | propertydecimal |
| 5 | propertystring |
| 6 | propertytext |
| 7 | propertydate |
| 8, 9, 10, 11 | propertydata |
Here's an example query:
select
p.id,
p.entity_name,
p.entity_id,
p.property_key,
p.propertytype,
n.propertyvalue as "Number value",
s.propertyvalue as "String value",
c.propertyvalue as "Decimal value",
t.propertyvalue as "Text value",
d.propertyvalue as "Date value",
x.propertyvalue as "Data value"
from propertyentry p
left join propertynumber n
on p.propertytype in (1, 2, 3)
and n.id = p.id
left join propertydecimal c
on p.propertytype in (4)
and c.id = p.id
left join propertystring s
on p.propertytype in (5)
and s.id = p.id
left join propertytext t
on p.propertytype in (6)
and t.id = p.id
left join propertydate d
on p.propertytype in (7)
and d.id = p.id
left join propertydata x
on p.propertytype in (8, 9, 10, 11)
and x.id = p.id
where
lower(p.property_key) like lower('%...%') /* REPLACE SEARCH TERM HERE */
OR lower(p.entity_name) like lower('%...%') /* REPLACE SEARCH TERM HERE, TOO */
order by
p.property_key ASC;It should output something like:
id | entity_name | entity_id | property_key | propertytype | Number value | String value | Decimal value | Text value | Date value | Data value
-------+-----------------+-----------+-----------------------------------------------+--------------+--------------+------------------------+---------------+------------+------------+------------
10824 | jira.properties | 1 | com.riadalabs.jira.plugins.insight:build | 5 | | 87 | | | |
10805 | INSIGHT-GENERAL | 1 | insight_batch_attribute_update_freq | 2 | 50 | | | | |
10826 | INSIGHT-GENERAL | 1 | insight_config_file_max_size | 2 | 10485760 | | | | |
10821 | INSIGHT-GENERAL | 1 | insight_config_name_desc_value | 5 | | The name of the object | | | |
10820 | INSIGHT-GENERAL | 1 | insight_config_name_value | 5 | | Name | | | |
10823 | INSIGHT-GENERAL | 1 | insight_config_servicedesk_search_text_multi | 5 | | Search for object/s | | | |
10822 | INSIGHT-GENERAL | 1 | insight_config_servicedesk_search_text_single | 5 | | Search for an object | | | |
10827 | INSIGHT-GENERAL | 1 | insight_default_objects_in_custom_field | 2 | 25 | | | | |
10831 | INSIGHT-GENERAL | 1 | insight_initial_version | 5 | | 10.0.13 | | | |
10832 | INSIGHT-GENERAL | 1 | insight_installed_version | 5 | | X | | | |
10830 | INSIGHT-GENERAL | 1 | insight_jira_issue_batch_size | 2 | 10000 | | | | |
10806 | INSIGHT-GENERAL | 1 | insight_locked | 1 | 1 | | | | |
10825 | INSIGHT-GENERAL | 1 | insight_new_icon_set | 5 | | TRUE | | | |
10828 | INSIGHT-GENERAL | 1 | insight_store_cache_file_on_shutdown | 1 | 1 | | | | |
10829 | INSIGHT-GENERAL | 1 | insight_themeable_icon_set_import_1 | 1 | 1 | | | | |