Suggestions requested

[sargasso]

I've inherited a system with about a zillion stored SQL queries in it.  (OK its only 5-6 dozen).  Its an access database app.  I need to document the design wvt migrating it to a .net app.  

Now here's the problem.  These queries are used as data sources in the miriad of forms and reports in the app.

Has anyone got a good idea as to how to go about structuring this?  How do I model a query?  How can I model its usage?

tia
bruce

[Paolo F Cantoni]

I've inherited a system with about a zillion stored SQL queries in it.  (OK its only 5-6 dozen).  Its an access database app.  I need to document the design wvt migrating it to a .net app.  

Now here's the problem.  These queries are used as data sources in the myriad of forms and reports in the app.

Has anyone got a good idea as to how to go about structuring this?  How do I model a query?  How can I model its usage?

tia
bruce

Hi bruce,

To quote Grady back at you ... "It depends".  What are you trying exactly to achieve?

As you know, profiles already exist for DB modelling in UML.  I'd initially start off by treating them all as views with a stereotype of the query type from MS Access (select/update/append/delete etc), providing associations back to the base table or views.

I don't think EA supports View (Query) Reverse Engineering yet, but I haven't looked for a few months...  But I'd  import the DB (if you haven't already)  

Can you elaborate?

Paolo

[moses]

funny ;-) I posted a similar question minutes ago :-)
one suggestion for the queries (not exactly pure UML, nor very elegant, but it works ....)

model / import the tables. then create a new class, then set it as a child of all the tables you need in your query, then show the inherited attributes and override the attribute initializers, saying things like 'not the appear' for each attribute of the inherited classes

[sargasso]

The issue is not the dbschema - already re'd and trasnscribed to postgres.

What I'm trying to do is come up with a documentation model that lets us understand the relationships between the queries (that 8 years of constant hacking have produced) and the UI thingos that depend on them.
I'm wrestling to find an appropriate way (read "easiest to create and use") to visualise:
a) the relationships between queries and UIs
b) the relationships between the queries and the tables (or vv).

Re a), we have already been stymied by trying to view this mess from the pov of "UI --> query"  as that answers the "this UI uses the following quieries", but not "we need/do not need to migrate this query as it is used by UI x".

A query can be used by 0..* UIs
A query can be used by a UI in 1..* "ways" (e.g. datasource, combolookup, filter, ...)
A query uses 0..* datatables and 0..* other queries.
A "query" model element needs to be taggable with "quality=good|medium|poor".

So I was looking for input, from someone who may have done this type of modelling before, of a "best practice" in their experience.


I'll give mojsis suggest a go and let you know.

I know d**n well this ain't proper UML like, but I gotta problem needs fixin'.

tia
bruce

[Paolo F Cantoni]

Bruce,

I was going to give you a raft of suggestions on how to do it, but I discovered that EA doesn't handle multiple inheritance properly, so you are stymied.

One problem you'll have to figure out how to solve (notwithstanding mojsis' suggestion - which does have merit) is how to handle aliases when one query is reusing the same table or view via aliases.

I was going to suggest creating an intermediate class for the «alias» and inheriting from each into the query class. But since EA doesn't handle attribute overriding properly this idea is non-functional (insert your favourite expletive here...).

Keep us posted as I'm interested in the refactoring issue also.

A few things I will suggest:
Use the targetRole of the «use» dependency of the GUI control on the query to hold the type of usage comboLookup etc.  Use the sourceRole for the name of the control (if required).
Document each use specifically, if multiple, between the two elements.
Add a "pattern" or "template" tagged value to assist in refactoring.
Use the Relationship Matrix to answer the "do we need to migrate" question (although that, too, is somewhat defective)
Where you refactor and no longer use a specific query but want to retain the linkage, set the targetRole Cardinality of the dependency to 0..0 (meaning do NOT use)

I'll log defects regarding the inheritance problems.

HTH,

Paolo

[Paolo F Cantoni]

Bruce,

I was going to give you a raft of suggestions on how to do it, but I discovered that EA doesn't handle multiple inheritance properly, so you are stymied.
[SNIP]

Also, although I thought mojzis' idea initially had merit, I now think it's fraught with danger (here be dragons... ).

A view is NOT a generalisation of the Table (or another view).  I'd use the Usage Dependency - perhaps with a stereotype.

Paolo

[CJ]

G'day Bruce,

Keep it simple.  Model you queries as classes (children of a "Query" class, if you need it).

Model your UI artifacts as classes as well (children of a "UI Artifact" class, if you need it).

Setup associations between a query and any UI artifact/query/<table>class, and use the relationship matrix.

How's that?

[TrtnJohn]

Maybe you should forget that there are queries at all and begin to re-analyze what the Use Cases of this system are.   I would hope the goals of moving the app to .NET would be to have better, more maintainable code.  If so, I'd just pretend the current implementation doesn't exist for now and start to abstract the system the way it should be based on the Use Cases.  Later in the process you can cut and paste the queries you'd like to reuse.

[Paolo F Cantoni]

  I would hope the goals of moving the app to .NET would be to have better, more maintainable code.  If so, I'd just pretend the current implementation doesn't exist for now and start to abstract the system the way it should be based on the Use Cases.

Thanks TrtnJohn for reminding us ALL (ME included - and perhaps especially!) about the right approach.  I've ALWAYS found getting agreement on WHY things are being done and starting afresh (at least with a preliminary analysis to see if there are any fundamental conceptual flaws with the current design) pays dividends in the long, medium and often short term...

Stop for a few minutes, take a deep breath, make sure everyone's singing from the same hymn book then go for it!

Paolo

[sargasso]

You can all rest assured.  Proper design of the new system is being done.

I'm trying to use the screwdriver as a chisel again.  What we dont understand about the current system is if and how these queries are being used.  Thus this exercise is just part of the larger redesign/redeploy project.

My problem is that I dont quite understand what the question I'm trying to answer is yet. (Let question = Q?)  So, this exercise is one of those "understand the problem" uses of UML/EA rather than a "design the solution" use.

If the queries were all just selects I'd not have an issue.  However, there are quite a few delete and update queries and some quite complex twists therein that people have used to get around the JET SQL problems.

It seems to be becoming clearer that Q? is behavioral rather than structural, or maybe its just that it is more behavioral than structural.

I am going to explore a few paths along that line today.

thanks to all for their input which has been of value. tracking down the illusive Q? beast!

bruce

p.s. TrntJohn, this is one of those "the design is the code" jobs.  Client expects both the conceptual and physical design of the new system to be "got" from the existing system.

[Paolo F Cantoni]

You can all rest assured.  Proper design of the new system is being done.

Never doubted it!  My comment was really a self-DOH!

p.s. TrntJohn, this is one of those "the design is the code" jobs.  Client expects both the conceptual and physical design of the new system to be "got" from the existing system.

On THAT topic, I thoroughly recommend:
http://www.developerdotstar.com/mag/articles/reeves_design_main.html
Which reinforces the notion of "Code as Design".
I have extended/plagiarised this concept with the notion of "Deployment as Architecture".

Paolo

[sargasso]

To be followed up shortly by "Reality as a Model"?  

[Paolo F Cantoni]

To be followed up shortly by "Reality as a Model"?  

Oh, bruce... SOOOO Profound!  I like it and I'm officially stealing it as of now!

Paolo