Re: parameterised SQL views

Bob Badour wrote:

> Going back to the earlier example, what would happen if the DateFrom and
> DateUntil columns were derived from data in a different table, and you had a
> view like:
>
> Create View Emp as
> select e.*
> , h.date as DateFrom
> , coalesce(t.date,'99991231') as DateUntil
> from ( employee e join job_record h
> on e.empid = h.empid
> ) left outer join job_record t
> on h.empid = t.empid
> and h.job_id = t.job_id
> where h.record_type = 'Hire'
> and t.record_type = 'Term'
> ;
>
> Consider how much more complex the Prolog might be without the view (Y10K
> problem notwithstanding).

I guess my tentative conclusion (offered up here for criticism, confirmation or refutation) is:

• views have uses, but they don't do everything I need (in particular, I can't parameterise them, 'cos then they wouldn't be materialisable (even 'though they'd still be relational, declarative and well-defined :-)
• I therefore need a more general mechanism: my best idea so far, and suggestions are very welcome, is to generate and "compile" SQL queries, with parameter markers, within each client session, and reuse them there
• with luck, there may be some fragments of SQL, common to several queries, which have no parameters, and refer only to tables or views; I can stash these in the database as views, as in your example
• more generally, there will be common fragments of SQL which *do* have parameters, or which refer to parameterised subqueries; there is no way I can save these in the RDBMS for sharing and reuse (as views or in any other way)
• I *must* find a way of factoring out the definitions of these fragments, or my applications will become unmanageable & unmaintainable
• so I'll define my parameterised "views" in Prolog, and also define my queries in Prolog (using these "views"), unroll the Prolog, translate it to SQL (with parameter markers), "prepare" it at runtime in each client instance, reuse it with different bindings as the client runs, then discard it when the client app dies

E.g. I could express your 'Emp' view in Prolog as

  emp( EmpId, EmpName, ..., DateFrom, DateUntil) :-

      employee( EmpId, EmpName, ...),
      job_record( EmpId, JobId, 'Hire', DateFrom),
      (   job_record( EmpId, JobId, 'Term', DateUntil)
      ->  true
      ;   DateUntil = 9999/12/31
      ).

and unroll it into my generated SQL query, although a smart "compiler" should spot that it is a candidate for saving as a view...

I've skimmed through Richard Snodgrass' recent "Developing Time-Oriented Database Applications in SQL" book, where he shows that, with ingenuity and some inelegance, it is possible to express a usefully complete set of temporal queries in SQL, but there is no suitable language construct for modularising and reusing the common code patterns; views are too restrictive, and stored procedures are too non-portable.

I can see parallels between SQL and PostScript, which was introduced as a programming (?) language, but with poor support for shared libraries, modularity and other "software engineering" aspects; now it is mostly used as an invisible intermediate language, mechanically generated and then mostly thrown away...

The temporal database community like to invent higher-level supersets of SQL for expressing their queries, and for compiling into ugly, long-winded "trad" SQL equivalents; my preference for Prolog is due to my preference for writing applications in Prolog (in practice, I mostly use VB, but that's another, even sadder, story :-)

Paul Singleton Received on Fri Oct 27 2000 - 17:26:01 CEST