Prev: Dictionary Compare for Alphanumeric Strings in TCL
Next: string length & regexp problem
From: InetDavid on 5 May 2010 13:49
On May 5, 1:06 am, "Donal K. Fellows"
<donal.k.fell...(a)manchester.ac.uk> wrote:
> On 4 May, 22:59, "tom.rmadilo" <tom.rmad...(a)gmail.com> wrote:
>
> > SELECT
> >  f_name || ' ' || l_name as full_name,
> >  last_four(ssn)
> >  from emp
> > order by
> >  upper(l_name)
> > where
> >  l_name like 'X%';
>
> > This requires a new type of object/record. This is a simple example,
> > table joins is another example. The point is that every query which
> > returns rows creates new object types/record types, so they require a
> > new mapping. The above SQL query above describes the mapping.
>
> Quite apart from the fact that your example probably gets the name
> composition wrong (names, like addresses[*], phone numbers and dates,
> are trickier than they appear to be at first) the query above does not
> actually describe a record. It describes an ordered collection of
> records.
>
> This means that the above would have to map to some kind of "list
> objects" operation that is not a method of the instances being
> returned. It's more like a constructor, except it's not really that
> either; it's a "list objects" method. :-) Luckily, if we look at how
> real ORM schemes work, they tend to include space for this sort of
> thing on the factory/discovery classes. If we were using TclOO, I'd be
> strongly tempted to make it into a method on the class object, perhaps
> to be called like this:
>
>   set emps [db::emp listByLastNameStartingWithX]
>
> OK, that's an ugly name but even so. Yes, the mapping systems that
> I've seen, such as EJB entity beans, do it this way. (Mind you, you'd
> need to fetch some kind of unique ID for each row too; otherwise
> there's no way to manage the identity of the returned objects.)
>
> > Select queries also have search parameters, how do you name them and
> > associate them with a column?
>
> You can't automatically do all that. ORMs do not expose all the
> features and power of a relational database automatically. OTOH, the
> practical ones do let you get control over the mapping so you can
> write the queries and have them within the system; it's more work than
> fully automated, but a lot more powerful too. I could certainly
> imagine that the above query might be generalized to:
>
>   set emps [db::emp findByLastName -match "X%"]
>
> Then I could do this:
>
>   foreach e $emps {
>      set n   [$e -full_name]
>      set ssn [$e -last_four_ssn_]; # OK, I remapped the name
>      puts "$n -> $ssn"
>   }
>
> (Damn! That might be a usable API!)
>
> I'd not expect writes back to the DB to work at all in this case,
> given that all the (non-ID) columns are the result of expressions.
>
> Donal.

Good catch, Donal. That's how ActiveRecord models handle cases like
that. Just define methods on the derived class to handle special
attributes. A more common method is to retrieve the entire record and
just create methods to display data in a different way. For example,
creating a "full_name" method to concatenate "first_name" and
"last_name" columns. This could be done in my TclOO version in the
derived class like this:

oo::class create Client {
superclass ActiveRecord::Base

# "Glue" to pick up ActiveRecord::Base class methods
classmethod unknown args { ::ActiveRecord::Base {*}$args }
oo::objdefine State unexport create

method full_name { {new_name {}} } {
if { $new_name != {} } {
my first_name [lindex $new_name 0] ;# Simple version for
example
my last_name [lindex $new_name 1]
} else {
return [concat [my first_name] " " [my last_name]]
}
}
}

Then you just retrieve the records as normal and access full_name from
the object(s):

% set client [Client find_by_first_name_and_last_name First Last]
oo::Obj6
% puts [$client full_name]
First Last
% $client full_name "New Name"
New Name
% puts [$client full_name]
New Name
% $client save

This allows you to *set* names by full name (which sets first_name and
last_name) and you can then *save* the records.

BTW, the "find_by_" method above *does* work now in my
implementation. It's a dynamic finder using method unknown and would
generate "SELECT * FROM `clients` WHERE `states`.`first_name` =
'First' AND `states`.`last_name` = 'Last' LIMIT 1".

David
From: tom.rmadilo on 5 May 2010 14:46
On May 5, 1:06 am, "Donal K. Fellows"
<donal.k.fell...(a)manchester.ac.uk> wrote:
> On 4 May, 22:59, "tom.rmadilo" <tom.rmad...(a)gmail.com> wrote:
>
> > SELECT
> >  f_name || ' ' || l_name as full_name,
> >  last_four(ssn)
> >  from emp
> > order by
> >  upper(l_name)
> > where
> >  l_name like 'X%';
>
> > This requires a new type of object/record. This is a simple example,
> > table joins is another example. The point is that every query which
> > returns rows creates new object types/record types, so they require a
> > new mapping. The above SQL query above describes the mapping.
>
> Quite apart from the fact that your example probably gets the name
> composition wrong (names, like addresses[*], phone numbers and dates,
> are trickier than they appear to be at first) the query above does not
> actually describe a record. It describes an ordered collection of
> records.

The collection is implicit, the record is defined in the first part of
the SELECT statement. A valid SELECT statement defines a collection of
zero or more records. Unless you include an ordering clause, the
collection is unordered. IMHO, every statement should include an
ordering clause.

> This means that the above would have to map to some kind of "list
> objects" operation that is not a method of the instances being
> returned. It's more like a constructor, except it's not really that
> either; it's a "list objects" method. :-) Luckily, if we look at how
> real ORM schemes work, they tend to include space for this sort of
> thing on the factory/discovery classes.

This isn't necessarily true. Any database system which returns this
result list of records (result table) could auto-generate objects
within the context of the caller. It could also return a list of these
objects if you need to iterate over them. This is my preference: a
query returns a list of references of created objects.

> If we were using TclOO, I'd be
> strongly tempted to make it into a method on the class object, perhaps
> to be called like this:
>   set emps [db::emp listByLastNameStartingWithX]
> OK, that's an ugly name but even so. Yes, the mapping systems that
> I've seen, such as EJB entity beans, do it this way. (Mind you, you'd
> need to fetch some kind of unique ID for each row too; otherwise
> there's no way to manage the identity of the returned objects.)

I would say that speaks well of EJB, but there is no guarantee that
the query objects can be updated. My example includes only one table,
but the system must be able to handle something more interesting:
joins.

Basically there are two separate problems that need to be solved with
ORM:

The first is simple mapping of a database record to some kind of list/
object in the programming language. This mapping can be easily
enhanced to provide any number of limited views of these records.

The second is generic queries, including most interesting select
queries. In this case, the query defines or must match the record
type. With Tcl, you can leverage the introspection and dynamic
capabilities to allow the query to define the record/object.

Unfortunately it is very difficult to match up these two. If we had
real SQL, as originally defined, then all views would be updateable
and the application developer would simply create views, but the fact
that no SQL database offers automatically updateable views should
inform programmers that the problem is difficult to solve.

> > Select queries also have search parameters, how do you name them and
> > associate them with a column?
>
> You can't automatically do all that. ORMs do not expose all the
> features and power of a relational database automatically. OTOH, the
> practical ones do let you get control over the mapping so you can
> write the queries and have them within the system; it's more work than
> fully automated, but a lot more powerful too. I could certainly
> imagine that the above query might be generalized to:
>
>   set emps [db::emp findByLastName -match "X%"]
>
> Then I could do this:
>
>   foreach e $emps {
>      set n   [$e -full_name]
>      set ssn [$e -last_four_ssn_]; # OK, I remapped the name
>      puts "$n -> $ssn"
>   }
>
> (Damn! That might be a usable API!)
>
> I'd not expect writes back to the DB to work at all in this case,
> given that all the (Send non-ID) columns are the result of expressions.

Your are describing what I call named queries. There is probably a
more formal name, but the basic idea is to create an interface to the
database system which removes SQL language from the user code, but
still allows "wrapped" SQL code which retains the full power of
whatever database you are using.

This also allows for multiple implementations without the need to
change user code. I like generic procedures, but your object based
interface ($e) would probably make it easier for end users to
specialize by selecting a different implementation.

I usually name queries like: moduleName::queryName, one example would
be dbis::SelectTableAttributes.

A named query just reflects a SQL view, which is also a named
query...although without parameters.
From: tom.rmadilo on 5 May 2010 16:01
On May 5, 10:49 am, InetDavid <inetda...(a)gmail.com> wrote:
> On May 5, 1:06 am, "Donal K. Fellows"
>
>
>
>
>
> <donal.k.fell...(a)manchester.ac.uk> wrote:
> > On 4 May, 22:59, "tom.rmadilo" <tom.rmad...(a)gmail.com> wrote:
>
> > > SELECT
> > >  f_name || ' ' || l_name as full_name,
> > >  last_four(ssn)
> > >  from emp
> > > order by
> > >  upper(l_name)
> > > where
> > >  l_name like 'X%';
>
> > > This requires a new type of object/record. This is a simple example,
> > > table joins is another example. The point is that every query which
> > > returns rows creates new object types/record types, so they require a
> > > new mapping. The above SQL query above describes the mapping.
>
> > Quite apart from the fact that your example probably gets the name
> > composition wrong (names, like addresses[*], phone numbers and dates,
> > are trickier than they appear to be at first) the query above does not
> > actually describe a record. It describes an ordered collection of
> > records.
>
> > This means that the above would have to map to some kind of "list
> > objects" operation that is not a method of the instances being
> > returned. It's more like a constructor, except it's not really that
> > either; it's a "list objects" method. :-) Luckily, if we look at how
> > real ORM schemes work, they tend to include space for this sort of
> > thing on the factory/discovery classes. If we were using TclOO, I'd be
> > strongly tempted to make it into a method on the class object, perhaps
> > to be called like this:
>
> >   set emps [db::emp listByLastNameStartingWithX]
>
> > OK, that's an ugly name but even so. Yes, the mapping systems that
> > I've seen, such as EJB entity beans, do it this way. (Mind you, you'd
> > need to fetch some kind of unique ID for each row too; otherwise
> > there's no way to manage the identity of the returned objects.)
>
> > > Select queries also have search parameters, how do you name them and
> > > associate them with a column?
>
> > You can't automatically do all that. ORMs do not expose all the
> > features and power of a relational database automatically. OTOH, the
> > practical ones do let you get control over the mapping so you can
> > write the queries and have them within the system; it's more work than
> > fully automated, but a lot more powerful too. I could certainly
> > imagine that the above query might be generalized to:
>
> >   set emps [db::emp findByLastName -match "X%"]
>
> > Then I could do this:
>
> >   foreach e $emps {
> >      set n   [$e -full_name]
> >      set ssn [$e -last_four_ssn_]; # OK, I remapped the name
> >      puts "$n -> $ssn"
> >   }
>
> > (Damn! That might be a usable API!)
>
> > I'd not expect writes back to the DB to work at all in this case,
> > given that all the (non-ID) columns are the result of expressions.
>
> > Donal.
>
> Good catch, Donal.  That's how ActiveRecord models handle cases like
> that.  Just define methods on the derived class to handle special
> attributes.  A more common method is to retrieve the entire record and
> just create methods to display data in a different way.  For example,
> creating a "full_name" method to concatenate "first_name" and
> "last_name" columns.  This could be done in my TclOO version in the
> derived class like this:
>
> oo::class create Client {
>   superclass ActiveRecord::Base
>
>   # "Glue" to pick up ActiveRecord::Base class methods
>   classmethod unknown args { ::ActiveRecord::Base {*}$args }
>   oo::objdefine State unexport create
>
>   method full_name { {new_name {}} } {
>     if { $new_name != {} } {
>       my first_name [lindex $new_name 0]   ;# Simple version for
> example
>       my last_name [lindex $new_name 1]
>     } else {
>       return [concat [my first_name] " " [my last_name]]
>     }
>   }
>
> }
>
> Then you just retrieve the records as normal and access full_name from
> the object(s):
>
> % set client [Client find_by_first_name_and_last_name First Last]
> oo::Obj6
> % puts [$client full_name]
> First Last
> % $client full_name "New Name"
> New Name
> % puts [$client full_name]
> New Name
> % $client save
>
> This allows you to *set* names by full name (which sets first_name and
> last_name) and you can then *save* the records.
>
> BTW, the "find_by_" method above *does* work now in my
> implementation.  It's a dynamic finder using method unknown and would
> generate "SELECT * FROM `clients` WHERE `states`.`first_name` =
> 'First' AND `states`.`last_name` = 'Last' LIMIT 1".

So how do you know that "full_name" is a list? What happens if
first_name is "Dum Asse"?

% llength [concat {Dum Asse} Jackson]
3

From: Arnold Snarb on 5 May 2010 17:26
tom.rmadilo wrote:

[ ... among other things ... ]

> If we had real SQL, as originally defined,
> then all views would be updateable

Wait, what?

I've heard griping that SQL doesn't implement
the True Relational Model (as envisioned by Codd,
prophesied by Date, and pounded into the ground
by Pascal); and I've heard grumblings that existing
RDBMSes don't even implement full SQL (as blessed
by ISO); but I have *never* heard that "Real SQL"
(or even that "Real Relational Databases") should
allow unrestricted updates through views.

That's just crazy talk!

In other words: "[citation needed]".


--Arnie
From: Arnold Snarb on 5 May 2010 17:44
Arnold Snarb just wrote:
> tom.rmadilo wrote:
>> If we had real SQL, as originally defined,
>> then all views would be updateable
> Wait, what?
> [...]
> but I have *never* heard that "Real SQL" should
> allow unrestricted updates through views.
>
> That's just crazy talk!

Well I'll be! That's actually Codd's Rule 6:

"All views that are theoretically updatable must be updatable"
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

I stand corrected.

My apologies to tom.rmadillo, I failed to read between the lines when
you said that in "real SQL [...] all {theoretically updatable} views
are {actually} updateable". The intersquigglebraced elisions
should have been obvious.

Sorry, won't happen again.


--Arnie
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Dictionary Compare for Alphanumeric Strings in TCL
Next: string length & regexp problem