SQL Power Software Forum

No, unfortunately not. I really want to implement a GUI for setting up arbitrary XSLT files. The GUI would be similar to the Database Connection Manager, and its entries would appear in a submenu under File. The settings would probably be stored in Java Prefs (I think this is less hairy than putting it in pl.ini, which is a legacy we're trying to escape).

-Jonathan

Jonathan wrote:No, unfortunately not. I really want to implement a GUI for setting up arbitrary XSLT files. The GUI would be similar to the Database Connection Manager, and its entries would appear in a submenu under File.

Sounds good.

The settings would probably be stored in Java Prefs (I think this is less hairy than putting it in pl.ini, which is a legacy we're trying to escape).

pl.ini does indeed seems rather strange. Good to hear that this is "deprecated"

castorp! You're on the right path for sure: your first pass demonstrates understanding the requirement. Hopefully the corrections will be simple to get it all the way, but I couldn't get far enough with it to propose the exact fix(es).

Attached are a couple documents we might be able to use to facilitate discussion. I've taken Jonathan's demo academy_awards.architect and added some labels (it's hard to discern function without documentation!). That and some screenshots are attached.

In the attached report snapshot, the thing ought to read something more like:
References
* nominee receives exactly one award
* nominee can be nominated for 0 or more pick
Referenced By
* award can be awarded to 0 or more nominee
* pick nominates exactly one nominee

I think that the label and cardinality selectors are just inter-swapped.. but I can't reliably report that, because my fix only worked so far. Perhaps using "entrant" (which has only 1 relationship defined in this model) would be a good starting point from which to continue as we hone the discrepancies toward the solution.

Really close!

You are really t'ing the xml with a deep understanding of the source xml.. Cool.

See the attached PDF of a partial solution to said problem. Now there are only entirely relationship labels missing. Take, for example, nominee: where are:
* award can be awarded to 0 or more nominee
* nominee can be nominated for 0 or more pick

This message was edited 2 times. Last update was at 2009-06-10 12:36:06

To get "the other half of the relationship statement" on the report, see the attached diff's between your posted xslt to my latest. Notable diffs:
* I included an "or" so as not to skip relationship labels (whereas you had it "I'm only dealing with fk's here and only with pk's here" in References and Referenced By respectively which left out the other half of the statements pairs.
* I crudely copied your entire first xsl:for-each and simply selected for the opposite (pk vs fk) statements
* added "" and neglected to remove it

Only major thing lacking is mashing these composite relationship statements together into a single, clockwise-read pair of statements. There's an xsl:sort-- would that help? to sort on fk-table-ref for one for-each adn pk-table-ref for the next?..

Thanks so much for your work on this: I wish I could contribute more actively than providing criticism in return ;)

-bronius

<edit>See attached screenshot showing two tables in the report with the improved but not perfected relationship labels</edit>
<edit>Made my "what's lacking" statement a little clearer (I hope)</edit>

Hi guys,

I thought I would provide a documented version of the awards model so I can follow along more easily. I've attached the documented Architect project, a screenshot of its playpen, and the report as generated by castorp's latest xslt on this thread (Bronius, I looked at your patch but didn't have the time to apply it just before lunch!)

Hope this helps.

-Jonathan

PS: if any business analysts can enlighten me about what to write for the two relations that go to an intersection entity, I'd appreciate that. I couldn't come up with anything reasonable, so I left them blank!

Thanks! Attached is the same report rerun with my requested XSLT changes. Something's fishy about the text between nominee and pick:
# pick "predicts nominee will win" exactly one nominee
should maybe read
# pick "predicts the winner will be" exactly one nominee

-bronius

Thanks, Bronius. I was having trouble with the wording there. I'm still trying to get the hang of this style of documentation.

Any idea about what to write for the lines going to/from intersection entities?

-Jonathan

I'd like to help, and I emailed a link to our BA a few hours ago but have heard nothing back. I think you are referring to the contest : series_contest : series tables, right?

Can you state it in English?
Ideally it might sound like: A series_contest maps contests with series contests. But there's no way to write that in this model..

Maybe:
A contest can be referred to by 0 or more series_contest
A series_contest is made of exactly 1 contest

A series_contest consists of exactly 1 series
A series can be made of 0 or more series_contest

Even if that is correct according to the model, it does sound a bit hokey (assuming my "English statement" above is correct)..

Yeah, series-series_contest-contest as well as award-award_pick_type-pick_type.

The latter is a much better example because the former is not correctly modeled. Series:Contest is actually a 1:many relation, so having that intersection entity there is not correct. We simply haven't had the cycles to change the applications involved, so that error in the data model is kind of entrenched.

The award:pick_type really is a many:many relation, so if you're asking around (thanks!), that's your example.

-Jonathan

award table comment is:

Represents some award or goal that several people or things (nominees) compete to earn as part of an overall contest. Only one one of those nominees will actually win the award. ...

award_pick_type table comment is:

Associates one or more pick types with an award. This determines how many (and which) picks are available to contest entrants for each award.

and pick_type table comment is:

For each award in a contest, the people entering the contest get one or more chances to predict which nominee will win each award. Picks can be weighted to make certain decisions worth more than others. ... Pick types are intended to be reusable between awards and even contests if desired. For example, we reuse a Primary Pick (valued at 2 points) and a Secondary Pick (valued at 1 point) for all awards of our Academy Award and Grammy Award contests.

so:
award is categorized into 0 or more award_pick_type
award_pick_type categorizes exactly 1 award
and:
award_pick_type associates exactly 1 pick_type
pick_type may be associated by 0 or more award_pick_type

This is based on my understanding of the model and its comments (THANKS for the comments!). Does it look right?

I will run this by our BA to see whether there is in fact some greater documentation style for "intersection tables," because I can see that while my simplistic statements accurately reflect table relationships, they don't convey the greater meaning of the tables triplet. Probably, however, he will recommend that that's what other documentation (table comments, model comments) is for.

award is categorized into 0 or more award_pick_type
award_pick_type categorizes exactly 1 award
and:
award_pick_type associates exactly 1 pick_type
pick_type may be associated by 0 or more award_pick_type

Yes, that's technically correct. But as we're both saying, the three tables and two FK constraints taken together make up the "intersection entity" idiom, i.e. they specify a many-to-many relationship.

Thanks for your help on this!

-Jonathan

Wouldn't you know.. Life presented Work, and that's what I've been doing all the while since this last conversation.

Obviously Power*Architect helped get my team a solid data model, and I was really hoping to see were some castorp-modified XSLTs per my latest comments on it.

Is there any way to jump-start this thread again? I think Jonathan and I side-spun it after my castorp-reply with post:
http://www.sqlpower.ca/forum/posts/list/30/1969.page#5001

Hey Bronius, good to hear from you again.

Since then, castorp has actually contributed a new "run a custom xslt or velocity template" feature to Architect. So you can run off reports using modified XSLTs from right inside the Architect GUI now. No more need for external XSLT tools.

As for the specific changes you're talking about, they sound like a good idea. The team at SQL Power is currently working flat out on Wabit, so I can't take that on at the moment.

Once again, good to hear from you again.

-Jonthan