Re: [Gnumed-devel] jl-gui et alii - v_emr_tree

[Jerzy Luszawski]

I have collected all database modification necessary for my plugins to
work in one file (attached), and pushed the update to my repository.

Here are some explanations:

*** clin.v_emr_tree ***

> > > CREATE OR REPLACE VIEW clin.v_emr_tree AS
(...)
> > > 1) Do you intend to apply the "ORDER BY 1,2,4,6" to the
> > >    entire view or to the second UNION sub query ? (if
> > >    it is the second sub-query the 6 seems unhelpful
> > >    as it is always NULL)
> > It refers to whole UNION, by specification of PostgreSQL, AFAIR. That's
> > why it requires column numbers instead of names.
> 
> Only if the column names of the UNION sub queries do not
> return identical column names (which are then transposed to
> the outer query) -- which they do in your view.
> 
> > It seems that the
> > first ORDER BY is superfluous, though. 
> 
> I agree.
I removed both ORDER BY clauses

> > > 2) Do you see problems with rewriting the view to
> > >    directly look at the base tables rather than
> > >    views thereof ? (that way this view will be more
> > >    robust when other views are modified)
> > AFAIK Postgres does good job by optimizing the views by itself.
> > Moreover it would probably require more nested SELECTS, exactly as the
> > base views do. I see no benefits (except getting independence from
> > other views, but in exchange for readability).
> 
> I see, yes, readability suffers a bit when looking directly
> at the base tables. I did not mean using per-column nested
> SELECTs, however (which I agree are not the best solution),
> but rather joining against the base tables.

For now I would rather leave this view as is because it worked for me
for long time, so I can assure everyone that everything based on it
will work. I will incorporate your suggestion from below and spend some
time with it for testing. What is the planned release date of future
database version?
I did rewrote one of other views (clin.v_pat_narrative_full), though.

> > > 4) What is the reason for choosing assessment to be the
> > >    description of the encounter ?  (this will severely limit
> > >    the generic applicability of this view)
> > Isn't assessment_of_encounter just this - short description?
> 
> I see, well, yes, that's right :-)
> 
> So perhaps I can suggest renaming encounter_description to
> encounter_assessment to make it very clear where this column
> takes its data from ?
I like my way of organizing data in pairs of (pk, description) for
health issue, episode and encounter to build EMR tree. It's just
cosmetics, but helpful for my development.

> > > 5) I think I don't fully understand which expected duplicity
> > >    you are protecting against with the sub-query global DISTINCT
> > >    on the first UNION sub-query ?
> > It is not obvious, I know :) I need *only encounters with at least one
> > narrative*, so I join with clin.clin_narrative, although I do not use
> > any of its fields. There can be many narratives... The alternative is
> > to explicitly look for existing narratives by SELECT in the WHERE
> > clause.
> 
> Maybe rephrasing helps me better understand: You need rows
> from clin.clin_narrative (but only the encounter and episode
> parts thereof) and expand upon those with data from
> clin.encounter, clin.episode, and, by extension,
> clin.health_issue. Is that correct ?
> 
> I would assume this query also expresses the desire:
> 
> SELECT DISTINCT ON (c_narr.fk_encounter)
>       c_enc.fk_patient as pk_patient,
>       c_epi.fk_health_issue as pk_issue,
>       c_hi.description as issue_description,
>       c_epi.pk as pk_episode,
>       c_epi.description as episode_description,
>       c_narr.fk_encounter as pk_encounter,
>       c_enc.assessment_of_encounter as encounter_description,
>       c_enc.started as encounter_started,
>       _(c_enc_t.description) as encounter_l10n_type
> FROM
>       clin.clin_narrative c_narr
>               LEFT JOIN clin.encounter c_enc on c_narr.fk_encounter = c_enc.pk
>                       LEFT JOIN clin.episode c_epi on c_narr.fk_episode = 
> c_epi.pk
>                               LEFT JOIN clin.health_issue 
> c_epi.fk_health_issue = c_hi.pk
>                                       LEFT JOIN clin.encounter_type c_enc_t 
> on c_enc.fk_type = c_enc_type.pk
> ;
> 
> Is that right ?
It looks right, seems to be equivalent and I will test it.

> (I think we don't want an INNER JOIN on clin.health_issue
> because there can be episodes w/o issues. We also don't need
> the more costly INNER JOIN from clin_narrative to
> clin.encounter or clin.episode because each are garantueed
> to be equivalent to a LEFT JOIN by design - AFAICT LEFT
> JOINS are less costly as PG only has to look at one side of
> the JOIN. Same with clin.encounter -> clin.encounter_type.)
> 
> Am I mistaken ?
Query EXPLAIN from pgAdmin does not show any differences when I
replaced LEFT joins with INNER, but I did no further research.

> > The general purpose of this wiew is to create pure hierarchical tree of
> > health_issue->episode->encounter. 

*** clin.v_pat_narrative_full ***
I need all fields from clin.narrative for EncounterEdit plugin and
don't need some of the fields provided by clin.v_pat_narrative, so I
created separate view.

*** clin.v_pat_encounters ***
I expanded the "standard" view by adding columns reflecting auditing.
All earlier columns are preserved, so I decided not to create
separate view.

*** Check: ref.paperwork_templates_engine_check ***
Added M: HTML and S:XSLT as possible values. This prepares
paperworks_templates for implementation of other methods of rendering
(and finally - printing) data, which I plan to reintroduce (did this
once, but now it requires some rewrite).
Since the number of possible "engines" increased substantially (there
were only four not so long ago), wouldn't it be better to move them to
another table and link to them?

*** GRANTs ***
We already discussed this.

*********

You may pull the file from my git tree, but if you prefer - I attach
the file directly.

-- 
Regards,
Jerzy Luszawski

v18-jl-gui_all_modifications.sql
Description: Text Data