Database columns and SQL queries

[M. King]

Greetings all.

I'm using EA 15.2, with an EAPX file for a repository. For my reporting, I wish to run some SQL queries against my model.

1 - How do I find out what columns are present in my database?

2 - I've tried opening the Database Builder to reach the "SQL Scratch Pad", so that I can try some test queries. But the Database Builder doesn't open my current model when it starts, and I can't see how to make it open the model. What do I need to do, to make the Database Builder query my currrent model? Or should I be using another tool to run ad-hoc SQL queries?


Pointers to online help documents are welcome - I'm fairly new to EA, and don't know where to look.

[Luigi2021]

The EAPX file is an Access Database as far as I know. So could open with any tool that supports Access and see the database structure. I don't know if EA lets you write SQL statements against an EAPX.

In the new EA 16 the EAPX is replaced by an SqLite DB. Maybe that is something to consider.

[Geert Bellekens]

1. You can reverse engineer the EA database into EA using the Database Builder Module. You'll need to create a DSN to your (or any other) EA repository

2. You can use the scratch pad in the model search to execute random SQL Queries. (you have to click on Edit and then there is a tab for the scratch pad)
The scratch pad is not the best SQL editing tool (it has very limited autocomplete functions). You can use your favorite SQL Query tool to do the same.
For .eapx files I use a free tool called AnySQLMaestro, but there are many other tools out there.

See also q's book abou the database structure: https://leanpub.com/InsideEA

Geert

[M. King]

Hello Geert.

Thank you for your swift answer, that's good to know... I'm hoping that EA's own functions will be sufficient to do what I wish.

Taking #2 first - I've reached the scratch pad in the model search, and I can run an SQL query. I'm modelling Requirements, using EA's UML extension for requirements.

I have 3 sets of Stakeholder requirements in a set of subpackages, and I'm defining System requirements to implement them. How do I tell that a requirement record is implemented, that is, what SQL query would I need in order to show Stakeholder requirements that have at least one linked System requirement linked to them? This is similar to the QA Report that shows which elements have been implemented (or not); my ultimate aim is to count the implemented ones so that I can produce a burnup chart as a timed report.

More widely - is the internal schema/structure of an EA database published anywhere?

[wivel]

More widely - is the internal schema/structure of an EA database published anywhere?

Not to my knowledge. The best resource, AFAIK, is the Inside EA book Geert is recommending. It is my go to whenever I need to write custum EA SQL.

Henrik

[Geert Bellekens]

Requirements (both System as Stakeholder) are elements, so they are stored in t_object.

Between them there is a connector; probably of type Realization; these are stored in t_connector.

You then join them on Start_Object_ID  and End_Object_ID

Geert

[qwerty]

There's only little written about EA's internal data formats in their help. It's not nothing, but very near to that. I once put my findings in my Inside book which now is the vademecum for anyone writing SQLs on EA's database.

q.