Re: S.O.D.A. database Query API - call for comments

 Hi guys!

  I'm reading this thread on http://www.deja.com and posting through my news.informix.com. Sorry for any confusion.

 1.
> Carl Rosenberger posted:
> > In response to what I posted:
> >
> > Just FYI: storing objects as attribute values is precisely what nearly
> > all modern (O)RDBMS products do. Some of them do it a *lot* better than
> > others. There is a query language standard (SQL-3) that sets out what the DML
> > looks like, and component standards (SQL-J, OLE) that explain how to link
> > components into the DBMS engine.
>
> Sorry, I really don't know this:
>
> How would you query for members of these objects?

    It all looks something like this (I'm fudgy on the syntax, because at the moment IBM, INFORMIX and Oracle use variations on this notation. But this captures the basic idea.) There are a number of references to this stuff, mostly out of date, but:

 http://www.objs.com/x3h7/sql3.htm

   Gets pretty close to the current flavor, although it isn't exactly right either.

   CREATE TYPE Guage_Reading (

                    Temp               Temperature    NOT NULL,
                    Height              Distance          NOT NULL

   This is the simplest form for creating an ADT/class. Done like this, all of the elements of the class (Temp, Height) are directly accessible. You could use a Java class to achieve the same goals. However, an object-class embedded into the engine using external languages like Java are very "encapsulated". You can *only* get to them using the methods/functions defined for them, and cannot access the elements directly.

   For more information on the SQL-J standard: http://www.sqlj.org/ Specifically look up references to SQLJ Parts 1 and 2 in the tutorials.

   CREATE TABLE Water_Flow_Guages (

        Id                Water_Flow_ID        PRIMARY KEY,
        Location       ST_POINT              NOT NULL,
        Flow             TIMESERIES ( Guage_Reading, "01/01/2001 10 minutes")

   The Flow column is the most contraversial thing, and some DBMS products support it but others don't. Water_Flow_ID and ST_POINT are pretty obvious. ST_POINT is the spatial type you would expect. (Do a google("st_point") and you will find out more than you really needed to know). Owing to the complexities of geographic information systems I'm omitting some details but this might simply encapsulate a lat/long point. The operations over the point are things like Get_X(), Get_Y(). Other things include Distance( ST_POINT, ST_POINT), which is a surprisingly complex little function, particularly if it is to return a result in 'yards' or 'miles'.

   SELECT W.Id,

                  Moving_Average ( W.Flow, "6 days")
      FROM Water_Flow_Guages W, States W
   WHERE S.Name = "Montana"
          AND Contains ( S.Boundary, W.Location)
          AND Moving_Average ( W.Flow, "60 days") < 0.75 *
                   Moving_Average ( W.Flow, "6 days");

   I think SQL-3 looks like this (not sure about the way the function referencing notation works: probably not like this, but an equivalent notation using "::" or something like it is followed).

   SELECT W.Id,

                  W.Flow->Moving_Average("6 days")
      FROM Water_Flow_Guages W, States W
   WHERE S.Name = "Montana"
          AND S.Boundary->Contains(W.Location)
          AND W.Flow->Moving_Average("60 days") < 0.75 *
                   W.Flow->Moving_Average ("6 days");

   All of the operations here -- polygon contains point, moving_average etc -- are clearly best implemented using procedural/OO languages. And both of these feature sets: the spatial extension and the timeseries extension are shipped in (to my knowledge) at least one commercial product.

> How would mass update statements look like to update properties (members) of
> these objects?

  UPDATE Water_Flow_Guages

          SET Flow = Flow->Add_Element( new Guage_Reading( new Temp(7.5, "cm"),

new Distance (11.5 "ft")))

   WHERE Id = :Some_Client_Variable;

  The interaction between the database and the client is a mite tricky. The best solution is the one enshrined in JDBC, where SQL types are mapped to Java classes automatically. Done right, this gives folk the impression that they are querying Java types.

  The essential difference between products involves how "low you can go". Most products allow you to make this kind of extension using their stored procedure language. That's OK, as far as it goes. But for serious work--compute the convex hull of these polygons, or the shortest path through this graph, or lift the following frames from this compresses MPEG-2 video--linking compiled code into the DBMS is necessary (this is what Postgres, INFORMIX, IBM DB2, and most OODBMS products do.) Plus, there are indexing problems, etc.

   [ Shameless plug: wanna read my book?

 http://www.amazon.com/exec/obidos/ASIN/1558604529/qid%3D989025360/002-1526694-9052009

   ]

> Could you provide respective examples?

  Does this lot help? Also, see the ADT thread.

  2.
> Todd Gillespie (toddg_at_linux127.ma.utexas.edu) asks:
>
> I see a question raised in your definitions, tho- 'stored procs do DML,
> functions don't.' Is that how the standard is defined? I've done DML as
> side effects in Oracle functions before, so.....(yes, I know this is bad.)

  No, it isn't how the standard is defined. It's just an application design thing. You can certainly put SQL into the functions that are called within SQL, but it's a *really* bad idea from a number of points of view. (And yes, I do it myself. It's a bit like NULLs (Hi Chris!!))

  Hope this helps.

  KR

               Pb Received on Sat Jul 21 2001 - 20:02:04 CEST