Extract Metadata information of a subclass
Hi All,
I want to know which tables should I use to extract metadata information for a subclass. For example if I want to know columns and their data types of Item, then which tables should I use to extract such information. So far I have done as follows, but I am not sure it is right.
select
*
from
(select
nt2.*
from nodetable nt2
inner join
(
select
nt.id
from
(
select
nt.id, nt.description
from nodetable nt
inner join (Select n2.id
from nodetable n1
inner join nodetable n2
on n1.id = n2.parentid
where 1=1
and n1.id = 5002
and upper(n2.description)=upper(‘Users’)
) n
on n.id = nt.parentid
) nt
where nt.description = ‘Attributes’
) n2
on nt2.parentid = n2.id
–where upper(nt2.description)=upper(‘General Info’)
)
Your expert advises will be really helpful.
Thank You.
To get a list of the possible attributes (and their ID) for a class, use the following query :
select n.id, n3.id, n3.description, n3.name
from nodetable n, nodetable n2, nodetable n3
where n.description = ‘<insert class name>’ and n2.parentid=n.id
and n2.description = ‘Attributes’ and n3.parentid=n2.id;
Note that all this will get you is the list of attributes available for the given class title/cover page and P2 attributes (P3 attributes are linked to the subclass – the query for the subclass attributes looks a LOT the same, but just starts from the subclass record instead of the class record). All other information about an attribute (whether it is visible, what list is it linked to, what is max length, what is the default value, etc.) can be obtained from PROPERTYTABLE, where the ID from nodetable will be PARENTID in Propertytable. Everything is arranged in a hierarchical mannger, going from base class, to class, to subclass, etc. Most of the basics in NODETABLE and most other information in PROPERTYTABLE (note I am *only* talking about classes, subclasses and attributes). You need to find out the values for OBJTYPE in NODETABLE (13 is subclass, 1 is attribute, 5 is class), as well as what the values in PROPERTYID mean (15 is the list assigned to the attribute, 10 is the database attribute name, 9 is whether it is visible or not, 2 is data type). To get all this data in a single query is nigh impossible, but that is why PL*SQL was invented – you start with the basic information and add what you need from there into a table.
In order to get attributes defined in TABs of a subclass I have done the following
SELECT *
FROM ( SELECT DISTINCT nClass.description AS SUBCLASS,
nLists.Description AS ATT_NAME,
nLists.Name AS ATT_API_NAME,
n2.description AS pageInfo
FROM nodetable nClass
INNER JOIN nodetable nAttribute
ON nClass.ID = nAttribute.parentid
AND nAttribute.description = ‘Attributes’
INNER JOIN nodetable nLists
ON nAttribute.id = nLists.Parentid
INNER JOIN nodetable n1
ON n1.id = nLists.inherit
INNER JOIN nodetable n2
ON n2.id = n1.parentid
ORDER BY nClass.description, nLists.Description)
WHERE SUBCLASS = ‘users’ AND pageinfo = ‘General Info’;
But I don’t know why on earth it does not show all the columns listed under General Info Tab. 🙁
there is a package pdq in agile database, you can extend it in java to pass parameters and call the procedure to extract needed data.
using Apachemeta model libraries and pdq packages one can extract data from agile efficiently.
Okay, more basic info. For each base class (Items, Changes, etc.) there are 5 things that are under it : Attributes, User Defined subclasses, Actions, Lifecycle Phases and Tabs. Under Attributes will be all base page/Page2 attributes. Under Lifecydcle Phases are all valid status/lifecycle values for the base class. Etc. “Tabs” lists the tabs that will show up for the base class. The Tab ID and class value can be used to show the attributes that are in that tab for that class.
select nt.description,na.id,na.description
from nodetable nt, nodetable na, tableinfo ti
where ti.classid=10000 and ti.tabid=nt.id and ti.att=na.id and nt.description=’Title Block’
order by nt.description,na.id;
The above query will list the Tab name, attribute ID and attribute name for all attributes in the Title Block tab for the Parts class. By using the subclass ID and correct tab name, you can get the P3 attributes listed for for each subclass as well. You still don’t know which ones are visible, but you know which ones *can* be in each tab.
Thank you Kevin. I have modified your query. It’s not so well organized but it does the job.
SELECT *
FROM (SELECT CLASS_ID,
ID,
TAB_NAME,
DISPLAYNAME,
NAME,
VALUE,
DATATYPE,
CASE
WHEN DATATYPE IS NOT NULL AND VISIBLE = 0 THEN 1
WHEN DATATYPE IS NOT NULL AND VISIBLE = 1 THEN 0
ELSE 0
END
AS VISIBLE
FROM (SELECT *
FROM ( SELECT DISTINCT
ti.classid CLASS_ID,
na.id,
nt.description TAB_NAME,
na.description DISPLAYNAME,
NA.NAME,
P.VALUE,
CASE
WHEN P.VALUE = 0 AND P.PROPERTYID = 2
THEN
‘Text/Multitext’
WHEN P.VALUE = 1 AND P.PROPERTYID = 2
THEN
‘List’
WHEN P.VALUE = 2 AND P.PROPERTYID = 2
THEN
‘Multitext’
WHEN P.VALUE = 3 AND P.PROPERTYID = 2
THEN
‘Numeric’
WHEN P.VALUE = 4 AND P.PROPERTYID = 2
THEN
‘Date’
WHEN P.VALUE = 5 AND P.PROPERTYID = 2
THEN
‘Money/Cost’
ELSE
”
END
AS DATATYPE,
p.visible
FROM nodetable nt,
nodetable na,
nodetable nLists,
–nAttribute.id = nLists.Parentid
tableinfo ti,
propertytable p
WHERE 1 = 1
AND ti.classid =
(SELECT id
FROM nodetable
WHERE 1 = 1 AND parentid = 5002
AND UPPER (DESCRIPTION) =
UPPER (‘Discussions’))
AND ti.tabid = nt.id
AND ti.att = na.id
AND UPPER (nt.description) =
UPPER (‘Discussion’)
AND P.PARENTID = na.id
AND p.propertyid IN (9, 2)
ORDER BY ti.classid)
))
WHERE DATATYPE IS NOT NULL