Prev: Dictionary Compare for Alphanumeric Strings in TCL
Next: string length & regexp problem
From: tomk on 24 Apr 2010 17:58
On Apr 23, 3:35 am, "Donal K. Fellows"
<donal.k.fell...(a)manchester.ac.uk> wrote:
> On 22 Apr, 21:06, "tom.rmadilo" <tom.rmad...(a)gmail.com> wrote:
>
> > Not going to disagree with off-base. But the reason large
> > organizations use relational databases is because of the ability to
> > normalize data and to query the data, producing new objects from the
> > base objects.
>
> You're not cynical enough. There's a *lot* of waste out there...
>
> > ACID just deals with creating and maintaining data in a
> > consistent state. Most of the power is in dynamic querying.
>
> Yes, but that's not how a lot of people are using things. In
> particular, the large group of people using ORMs tend to avoid the
> real power. Also, a lot of database installations are optimized to not
> handle ad hoc queries well. Yes, a relational database is overkill in
> this situation; when did that ever stop a poor programmer?
....snip...
>
> Donal.

Another point of view is that relational databases are the easiest to
restructure and optimize (which is my point of view). I started out
with files of "stuff" then went to a hierarchical database, then to a
networked database and finally to a relational database. The database
architectures were constrained by the current platform performance
which improved over time. But for me (the applications developer) the
real goal was flexibility because my users always wanted changes that
effected the database. And the users wanted integrity (which is more
about maintenance that features) and speed (which was usually a
distance second to capability).
tomk
From: tom.rmadilo on 24 Apr 2010 18:20
On Apr 23, 3:35 am, "Donal K. Fellows"
<donal.k.fell...(a)manchester.ac.uk> wrote:
> On 22 Apr, 21:06, "tom.rmadilo" <tom.rmad...(a)gmail.com> wrote:

>
> > The original schema must have unique column names for base tables, but
> > not so for derived tables or queries. Solving the base table interface
> > is not interesting, although it is important. Solving the select query/
> > result table interface is difficult.
>
> As long as you've got *some* control over the query, you can alias the
> columns to ensure uniqueness of names in the result. That's all you
> really need. (The aliases don't change the query, just the
> interpretation of it presented in the result set.)
>

Right, I'll explain what I mean below

> > The above isn't quite true. Any time you have to deal with multiple
> > classes of users with different permissions on tables and columns, you
> > run into real trouble with direct relational to object mapping. You
> > need an intermediate mapping. Fortunately such a mapping can double as
> > an user interface definition.
>
> That's a subtle thing to do well, especially when the accessing
> application (i.e., the one with the object model) is serving multiple
> users at once.
>
> > This still doesn't help with select queries.
>
> Do you mean the naming problem (a non-issue really) or something else?

A simple mapping allows a 1-to-1 relationship between the programming
language objects and the relational model. This is not usually
sufficient for moderately interesting applications. However, with
another type of mapping, which I called an intermediate mapping, you
can get 1-to-N mappings from one underlying relational model, exploded
into N object models. This only works if the new objects are strict
subsets of the relational model, so it isn't like OO inheritance.

With both types of mappings, there is no confusion on naming of the
object or the attributes, since they are created together.

What doesn't work is trying to reuse this information in a query
result created by a select (except "select cols from table where id =
$id"). As soon as you require a subset of all columns, a computed or
formatted column, or a multi-table join, which effectively creates a
new class of objects, you have to start over and create a new object
mapping. The most efficient language to create this mapping is an SQL
select statement, where you can choose the result column names. Select
queries also have search parameters, which are distinct from the
object/result attributes. However, an SQL query cannot fully replace a
mapping. One way around this is to use either an SQL procedure
interface, or a programming language interface to specify the full
details (or a specification language/mapping which could be used to
auto-generate either). You could remove the SQL query from this by
defining views within the database to represent the select query.

From: tomk on 4 May 2010 16:27
On Apr 22, 3:46 pm, "tom.rmadilo" <tom.rmad...(a)gmail.com> wrote:
> On Apr 22, 1:26 am, "Donal K. Fellows"
>
> <donal.k.fell...(a)manchester.ac.uk> wrote:
> > On 21 Apr, 18:52, InetDavid <dav...(a)tower-mt.com> wrote:
> > > As to the other discussion, I'm just going to ignore it since
> > > ActiveRecord does *work* for Ruby on Rails just fine, in spite of
> > > uninformed, contrary claims.  :-)
>
> > I suspect that there are theoretical edge cases where it doesn't work,
> > but production code can just ignore them as being irrelevant. ;-)
>
> You want to know why Tcl isn't taken seriously? The above bullshit
> should explain it.
>
> You either model the details and succeed or you simplify and fail.
>
> This point seems impossible to explain to pure programmers. A base
> table is like a generic object. Most applications require subtyping of
> these objects, so you need an additional layer of definition/mapping.
>
> If anyone needs examples, ask me.

Since you offered, please give me an example of a query on a database
(containing tables with unique column names) that can't be performed
by an Active Record.
tomk
From: tom.rmadilo on 4 May 2010 17:59
On May 4, 1:27 pm, tomk <krehbiel....(a)gmail.com> wrote:
> On Apr 22, 3:46 pm, "tom.rmadilo" <tom.rmad...(a)gmail.com> wrote:
>
>
>
>
>
> > On Apr 22, 1:26 am, "Donal K. Fellows"
>
> > <donal.k.fell...(a)manchester.ac.uk> wrote:
> > > On 21 Apr, 18:52, InetDavid <dav...(a)tower-mt.com> wrote:
> > > > As to the other discussion, I'm just going to ignore it since
> > > > ActiveRecord does *work* for Ruby on Rails just fine, in spite of
> > > > uninformed, contrary claims.  :-)
>
> > > I suspect that there are theoretical edge cases where it doesn't work,
> > > but production code can just ignore them as being irrelevant. ;-)
>
> > You want to know why Tcl isn't taken seriously? The above bullshit
> > should explain it.
>
> > You either model the details and succeed or you simplify and fail.
>
> > This point seems impossible to explain to pure programmers. A base
> > table is like a generic object. Most applications require subtyping of
> > these objects, so you need an additional layer of definition/mapping.
>
> > If anyone needs examples, ask me.
>
> Since you offered, please give me an example of a query on a database
> (containing tables with unique column names) that can't be performed
> by an Active Record.
> tomk

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.

Select queries also have search parameters, how do you name them and
associate them with a column?

The most general solution is to use a procedure-like abstraction, such
as stored procedures, prepared statements, call level interface, etc.

A simple mapping like ActiveRecord can still do a lot of work, but the
same mapping could be used to generate the equivalent stored
procedures.

This stuff is much easier to do in Tcl: the result records can be used
to generate objects using any Tcl OO system you wish, or you could
just generate a list-of-lists, or a list-of-arrays.






From: Donal K. Fellows on 5 May 2010 04:06
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.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Dictionary Compare for Alphanumeric Strings in TCL
Next: string length & regexp problem