RE: [Webware-discuss] Minimal object-relational mapper...?

[Edmund L. <el...@in...>]

Ian wrote:

>> 2. Support for transactions
>
>I didn't do this, but it would be nice... what kind of interface do you
>use for this?  In a multi-threaded environment, I'd be unsure about how
>to lump together transactions... unless, I suppose, you have a
>connection object associated with each thread's accesses.

Yes. This seems to be the only way to do it since the DB API does
transactions at the connection object level rather than say, the cursor
level.


>I started with the thinest of wrappers and have filled it out some as I
>feel inspired... I haven't felt inspired enough to do joins, but I'm
>getting closer.  I'd want to do a lot of caching, but keeping the cache
>valid is a significant problem.  The many-to-many joins, or other
>non-obvious joins, are a bit more difficult to properly abstract... but
>then those joins maybe should be left up to the implementor.

I think the cacheing problem is insurmountable in a DBMS independent way.
With PostgreSQL, you could have the table notify you when it is updated,
but no such facility exists with say, Oracle unless you use a trigger
coupled to a stored procedure.

After struggling with the semantics of a join, I've come to the conclusion
that joins cannot be handled using the same kind of object as one
representing a single table. In any case, an object representing two or
more joined tables (really just a view) can't accept row modification or
deletion methods since current DBMS products don't allow you to
write-through views. Well, maybe Oracle EE does since it has materialized
views, but who can afford this anyway?

>Have you looked at the query-generation wrapper I wrote?  I'm not
>entirely sure how successful the idea was, but it could create queries
>from Python expressions.  You had to use & and | instead of "and" and
>"or", since and/or can't be overloaded, but it was otherwise fairly
>similar to regular Python expressions -- unfortunately, & and | don't
>have the right order of precedence.

Hmmm... never got the message containing the code, and SF doesn't store
attachments (your post is here:
http://www.geocrawler.com/mail/msg.php3?msg_id=8240549&list=3854)

Could you email them to me directly so I can look at them?


>Is each row a distinct object, with accessors and all?  How do you add
>extra methods to these objects?

No, in the examples I gave, x = DBTable('table_name'), which just
instantiates an object to mediate between the table in question and my
code. Whether the .delete(), .update() methods operate on an individual row
or a bunch of rows depends on the specificity of the conditions named in
the .update() and .delete() methods.

I did it this way because if you treat each row as a distinct object with
accessors and methods, you will devolve down to procedural code when you
need to modify a bunch of similar rows (e.g., update user_table set
group_name = 'apple' where group_name = 'orange') When you do this, you
lose the advantages of using declarative languages like SQL to begin with.


>> # Return just col_one and col_two of a certain subset:
>> x.columnList = ['col_one', 'col_two']
>> x.select(where="id=1 and name='apple'")
>
>Is x like a connection, and columnList effects the next .select
>statement?  That seems rather unfunctional -- why not just put it in the
>select method call?

x is just an object representing one table. columnList does affect the next
.select statement right now. I did this because x, as an object, has state
information. It knows what columns it has, and what columns to retrieve. I
want to separate the "I know what columns to retrieve" from the "I know
which rows to retrieve" behavior to ease the syntax in making calls--I'm
trying to make it as painless as possible to interact with the tables.

I'm just rewriting everything again now since I think I've found a more
natural way to express some of the semantics in these method calls.

...Edmund.