extracting data from the repository with SQL - "is id" flag

[Ru]

Hi Folks.

I'm trying to pull data (specifically class definitions) from the EA repository. I've found most things, but I can't track down the location of the "is id" attribute flag. Anyone have an ideas? I cannot find any documentation on the repository data model (ironic really).

PS - does anyone know what SQL engine is built into the desktop app? It seems... finicky.

[qwerty]

The isId is in the t_xref in name=CustomProperties with type "attribute property". You need to look up the @PROP CSV data in the description column.

EAP = Mickeysoft Access

q.

[philchudley]

Hi

This is a trick one

If an attribute has isId set to true, then an entry  is made in the the table t_xref, which can be verified by

SELECT t_xref.Description
FROM t_xref, t_attribute
WHERE t_xref.Client = t_attribute.ea_guid

And the value in t_xref.Description is

Description,

@PROP=@NAME=isID@ENDNAME;@TYPE=Boolean@ENDTYPE;@VALU=1@ENDVALU;@PRMT=@ENDPRMT;@ENDPROP;

You can see that @NAME=isID@ENDNAME;@TYPE=Boolean@ENDTYPE;@VALU=1@ENDVALU; verifies that this attribute has the isID property set to true

Hope this helps

Phil

[Geert Bellekens]

Here's an example query I use to list all classes and attributes (including isID)

Code: [Select]

SELECT c.ea_guid AS CLASSGUID, c.Object_Type AS CLASSTYPE,
 c.Name AS [Class name], NULL AS [Attribute name],
'Class' AS [Type], CASE WHEN c.Abstract = '0' THEN 'no' ELSE 'yes' END AS [Abstract], shared.Value AS [Shared],
NULL AS [M], NULL AS [ID], ts.Value AS [T], v.Value AS [V], dc.Value AS [DC], NULL AS [Datatype], 0 AS Pos,
p.Name AS [Package], p1.Name AS [Package +1]
FROM (((((((t_object c
LEFT JOIN t_objectproperties shared ON (shared.Object_ID = c.Object_ID AND shared.Property = 'Shared'))
LEFT JOIN t_objectproperties v ON (v.Object_ID = c.Object_ID AND v.Property = 'Versioned'))
LEFT JOIN t_objectproperties ts ON (ts.Object_ID = c.Object_ID AND ts.Property = 'Timesliced'))
LEFT JOIN t_objectproperties dc ON (dc.Object_ID = c.Object_ID AND dc.Property in ('Data Classification')))
LEFT JOIN t_object op ON op.Object_ID = c.ParentID)
INNER JOIN t_package p ON c.Package_ID = p.Package_ID)
LEFT JOIN t_package p1 ON p1.Package_ID = p.Parent_ID)
WHERE c.Object_Type = 'Class'
AND p.Package_ID IN (#Branch#)
UNION
SELECT a.ea_guid AS CLASSGUID, 'Attribute' AS CLASSTYPE,
 c.Name AS [Class name], a.Name AS [Attribute name],
'Attribute' AS [Type], CASE WHEN c.Abstract = '0' THEN 'no' ELSE 'yes' END AS [Abstract], shared.Value AS [Shared],
a.LowerBound + '..' + a.UpperBound AS [M], CASE WHEN x.[Description] IS NULL THEN 'no' ELSE 'yes' END AS [ID], ts.Value AS [T], v.VALUE AS [V], dc.Value AS [DC], a.Type AS [Datatype], a.pos AS Pos,
p.Name AS [Package], p1.Name AS [Package +1]
FROM ((((((((t_attribute a
INNER JOIN t_object c ON a.Object_ID = c.Object_ID)
LEFT JOIN t_objectproperties shared ON (shared.Object_ID = c.Object_ID AND shared.Property = 'Shared'))
LEFT JOIN t_attributetag v ON (v.ElementID = a.ID AND v.Property = 'Versioned'))
LEFT JOIN t_attributetag ts ON (ts.ElementID = a.ID AND ts.Property = 'Timesliced'))
LEFT JOIN t_attributetag dc ON (dc.ElementID = a.ID AND dc.Property in ('Data Classification')))
LEFT OUTER JOIN t_xref x ON (x.Client = a.ea_guid
AND x.Type = 'attribute property'
AND x.Description LIKE '%@PROP=@NAME=isID@ENDNAME;@TYPE=Boolean@ENDTYPE;@VALU=1@ENDVALU;%'))
INNER JOIN t_package p ON c.Package_ID = p.Package_ID)
LEFT JOIN t_package p1 ON p1.Package_ID = p.Parent_ID)
WHERE c.Object_Type = 'Class'
AND p.Package_ID IN (#Branch#)
ORDER BY [Class name], Pos, [Attribute name]This query works for SQL Server, you might need to tweak it a bit for MS Access syntax.

Geert

[Ru]

Thanks all - I would never have figured that out myself.

Now I just need to get around MS Access' syntactic peculiarities :/

[Geert Bellekens]

Now I just need to get around MS Access' syntactic peculiarities :/

Or you could switch to SQL Server or any of the other supported DBMS (as long as you don't go for Oracle)

Geert

[Ru]

My colleague is working on it - unfortunately getting infrastructure sorted out in our company is slower than that Australian pitch drop experiment.