sqlobject-discuss — All things SQLObject-related

[SQLObject] Inserting Into Views With PostgreSQL

[CLIFFORD I. <cli...@di...>]

Hi,

I really like the way that Oracle treats views as if they were virtual 
tables and will allow one to do INSERT, UPDATE, and DELETE operations on 
them and I equally dislike the way that PostgreSQL deals with views. It is 
NOT straightforward at all. Is there some way using SQLObject that we can 
abstract out the awkwardness of views in PostgreSQL. For example, say we 
have a classic master/detail relationship, invoice headers and invoice 
details. In Oracle, I would create a view called invoices and simply treat 
it as if it were a table. Can I do something like the following in SQLObject?

class(Invoice):
         <define "view" like structure here>

inv = Invoice.new(bla, bla, bla)

Some of these views can abstract a fairly complex relationship consisting 
of many tables being joined. It is much nicer having to deal with the 
abstraction than having to deal with the underlying tables.

Regards,

Clifford Ilkay
Dinamis Corporation
3266 Yonge Street, Suite 1419
Toronto, Ontario
Canada M4N 3P6

Tel: 416-410-3326 

[SQLObject] Dealing With PostgreSQL Constraints

[CLIFFORD I. <cli...@di...>]

Hi,

How does SQLObject deal with constraint violations? Does it intercept the 
error and present some meaningful interface via Python?

Regards,

Clifford Ilkay
Dinamis Corporation
3266 Yonge Street, Suite 1419
Toronto, Ontario
Canada M4N 3P6

Tel: 416-410-3326 

Re: [SQLObject] How to insert a boolean into PostgreSQL

[Luke O. <lu...@me...>]

>> ValueError: Unknown SQL builtin type: <type 'instance'> for TRUE
>
> Hmm... are you using an early version of Python 2.2?  I think there was
> a problem related to that version (before True and False were defined)

Actually, as I recall this is a combination of using an older version of 2.2
and a slight bug in the workaround class in SQLObject. Take a look in
Converters.py, the definition for class BOOL. It should be defined as a
new-style class - class BOOL(object). But at least in some versions of
SQLObject this was missing, leading to the error above.

- Luke

Re: [SQLObject] How to insert a boolean into PostgreSQL

[Ian B. <ia...@co...>]

On Feb 20, 2004, at 1:29 AM, CLIFFORD ILKAY wrote:
> I was able to create a PostgreSQL table with a boolean column by doing 
> myClass.createTable but I cannot insert into it without SQLObject 
> raising the following error:
>
> ValueError: Unknown SQL builtin type: <type 'instance'> for TRUE

Hmm... are you using an early version of Python 2.2?  I think there was 
a problem related to that version (before True and False were defined)

> I am attempting to insert as follows:
>
> p = person.new(firstName="Donald", lastName="Duck" , amtPaid="10.26", 
> isParticipant=True)
>
> I tried assigning "", "T", 1 to isParticipant but all behaved the same 
> way. NULLs are allowed for the boolean column. How can I insert into 
> this table?
>
> Also, how would I specify a default value for a boolean column? I 
> tried:
>
> isParticipant=BoolCol(default=True)
>
> and when I created the table, there was no default for the column.

The default only applies to the object, not the table.  When you add a 
row through SQLObject the default is used.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org

[SQLObject] How to insert a boolean into PostgreSQL

[CLIFFORD I. <cli...@di...>]

Hi,

I was able to create a PostgreSQL table with a boolean column by doing 
myClass.createTable but I cannot insert into it without SQLObject raising 
the following error:

ValueError: Unknown SQL builtin type: <type 'instance'> for TRUE

I am attempting to insert as follows:

p = person.new(firstName="Donald", lastName="Duck" , amtPaid="10.26", 
isParticipant=True)

I tried assigning "", "T", 1 to isParticipant but all behaved the same way. 
NULLs are allowed for the boolean column. How can I insert into this table?

Also, how would I specify a default value for a boolean column? I tried:

isParticipant=BoolCol(default=True)

and when I created the table, there was no default for the column.

Regards,

Clifford Ilkay
Dinamis Corporation
3266 Yonge Street, Suite 1419
Toronto, Ontario
Canada M4N 3P6

Tel: 416-410-3326 

RE: [SQLObject] proxy attributes

[<jws...@ra...>]

>class CustomerAccount(SQLObject):
>    # private composition
>    _buyer = ForeignKey('Buyer')
>
>    # public proxy
>    def _get_emailAddr(self):
         ^^^^^^^^^^^^^^^^^^^^ 

>        # this could also have proxy at Buyer obj to buyerInfo, so like:
>        # return self._buyer.emailAddr ==
>        return self._buyer.buyerInfo.emailAddr
>
>
>makes this work:
>
>x = CustomerAccount(id)
>print x.emailAddr

I don't understand this magic, but it seems to be working for me. I still 
have to specify all my classes by hand, though. 

If I do a dir() on an instance of my class, I get a list of ALL private
and public attributes. What is the best way for a program containing my 
SQLOject to get a list of the public attributes that object exposed in 
it's definition, excluding private and inherited items? The printable  
representation of the object looks like 

	<Customer 6 name='ACME' buyerID=1>

'_columns' lists the real columns, but not the proxied ones. I could also
just add a method in my class to return a list of fields. I'm thinking 
of generating a gui form based on what the object tells me it's fields are.

Re: [SQLObject] Re: Re: Question about multiple deletes

[Ian B. <ia...@co...>]

Chris Gahan wrote:
>>>There's nothing in SQLObject right now that does this, but you could add
>>>your own method pretty easily...
>>>
>>>Just add a function to the SQLObject class called .multiDelete() or
>>>something, which works the same as .select().. (i.e. takes in the same
>>>parameters... obviously it would do something quite different. ;)
>>
>>Oops, guess I missed this message.
>>
>>It's a little more complicated than that, because a delete should really
>>go through the SQLObject instances.  Selects should be extended so you
>>can retrieve IDs instead of full objects, then there should be a way to
>>fetch an object only if it already exists in Python.  Then you'd iterate
>>over the select results and call destroySelf on any objects that
>>existed, and then do another SQL statement to delete any rows that
>>didn't have Python instances.
> 
> 
> Wow, yeah, I didn't think about object-to-database consistency. What a
> bitch. :)
> 
> Object databases are hard!

Actually, while there's a bunch of details, none of them are 
particularly difficult.  Everything's there, a few new APIs just have to 
be exposed (to get IDs, and to get objects from the cache)

> Hmmm.. couldn't you have a simpler method though, where the object was
> ignorant of its being deleted until it actally tried to sync itself with the
> database? I mean, what's the reason for using destroySelf to kill objects?
> Is it to keep the cache clean?

Actually, I think they might stay in the cache now (though obviously 
they shouldn't).  As a basic principle, destroySelf should be called so 
that objects can clean themselves up.  This allows for things like 
cascading deletes on databases that won't do it for you.  (But it also 
means we have to calculate the cascades even on databases that do 
support doing it themselves)

   Ian

[SQLObject] Re: Re: Question about multiple deletes

[Chris G. <ch...@il...>]

> > There's nothing in SQLObject right now that does this, but you could add
> > your own method pretty easily...
> >
> > Just add a function to the SQLObject class called .multiDelete() or
> > something, which works the same as .select().. (i.e. takes in the same
> > parameters... obviously it would do something quite different. ;)
>
> Oops, guess I missed this message.
>
> It's a little more complicated than that, because a delete should really
> go through the SQLObject instances.  Selects should be extended so you
> can retrieve IDs instead of full objects, then there should be a way to
> fetch an object only if it already exists in Python.  Then you'd iterate
> over the select results and call destroySelf on any objects that
> existed, and then do another SQL statement to delete any rows that
> didn't have Python instances.

Wow, yeah, I didn't think about object-to-database consistency. What a
bitch. :)

Object databases are hard!

Hmmm.. couldn't you have a simpler method though, where the object was
ignorant of its being deleted until it actally tried to sync itself with the
database? I mean, what's the reason for using destroySelf to kill objects?
Is it to keep the cache clean?

Re: [SQLObject] Support for Long

[Ian B. <ia...@co...>]

Michael Watkins wrote:
> I have been toying around with SQLObject for a couple days - I've used
> other Python ORM's fairly extensively (Object Relational Membrane; and
> also a little known tool called Cucumber by Titus Brown) and while I
> like bits and pieces of all of them, I must say I am in love with the
> table generation from SQLObject classes. What a pleasure not to have to
> maintain (mostly) seperate schema definitions.
> 
> Anyway... I miss support for longs so I hacked up Converters.py and
> Constraints.py to add Long support, which seems to work ok, but wonder
> if there was a reason not to include long support in the first place or
> just an oversight?

You mean like Python long integers?  Yeah, just an oversight -- I hadn't 
encountered them in any of my code, so I never noticed.

   Ian

Re: [SQLObject] Re: Question about multiple deletes

[Ian B. <ia...@co...>]

Chris Gahan wrote:
> "David McNab" <da...@re...> wrote in message
> news:402...@re......
> 
>>What's the best way, within SQLObject, to delete an entire results set
>>from a table?
>>
>>In other words, the equivalent of an SQL query like:
>>
>>   DELETE FROM mytable WHERE last_name = 'Jones' AND age > 50
>>
>>I know I can iterate through an SQLObject result set and invoke
>>.destroySelf() on every element, but this feels painfully inefficient.
> 
> 
> There's nothing in SQLObject right now that does this, but you could add
> your own method pretty easily...
> 
> Just add a function to the SQLObject class called .multiDelete() or
> something, which works the same as .select().. (i.e. takes in the same
> parameters... obviously it would do something quite different. ;)

Oops, guess I missed this message.

It's a little more complicated than that, because a delete should really 
go through the SQLObject instances.  Selects should be extended so you 
can retrieve IDs instead of full objects, then there should be a way to 
fetch an object only if it already exists in Python.  Then you'd iterate 
over the select results and call destroySelf on any objects that 
existed, and then do another SQL statement to delete any rows that 
didn't have Python instances.

   Ian

[SQLObject] Support for Long

[Michael W. <mw...@mi...>]

I have been toying around with SQLObject for a couple days - I've used
other Python ORM's fairly extensively (Object Relational Membrane; and
also a little known tool called Cucumber by Titus Brown) and while I
like bits and pieces of all of them, I must say I am in love with the
table generation from SQLObject classes. What a pleasure not to have to
maintain (mostly) seperate schema definitions.

Anyway... I miss support for longs so I hacked up Converters.py and
Constraints.py to add Long support, which seems to work ok, but wonder
if there was a reason not to include long support in the first place or
just an oversight?



--
Mike Watkins
mw...@mi...


For a man to truly understand rejection, he must first be ignored by a
cat.

[SQLObject] Re: Question about multiple deletes

[Chris G. <ch...@il...>]

"David McNab" <da...@re...> wrote in message
news:402...@re......
> What's the best way, within SQLObject, to delete an entire results set
> from a table?
>
> In other words, the equivalent of an SQL query like:
>
>    DELETE FROM mytable WHERE last_name = 'Jones' AND age > 50
>
> I know I can iterate through an SQLObject result set and invoke
> .destroySelf() on every element, but this feels painfully inefficient.

There's nothing in SQLObject right now that does this, but you could add
your own method pretty easily...

Just add a function to the SQLObject class called .multiDelete() or
something, which works the same as .select().. (i.e. takes in the same
parameters... obviously it would do something quite different. ;)

[SQLObject] Bad connection timeout behaviour...

[Chris G. <ch...@il...>]

What's the deal with the connection timeout behaviour?

I imported an SQLObject I created called Users, selected a couple items from
it, then went away for a while. When I came back and selected another item
from it, it threw an exception saying that the connection had timed out. It
didn't try to reconnect, as you'd expect, but just died.

Then I did another query right afterwards, and it reconnected to the
database and worked fine! This isn't very nice. It should really throw an
exception if it CAN'T reconnect, not if it HAS to reconnect.

Here's an example:

[ ... leave for long time and come back ... ]
>>> u = Users(1)
  File "<console>", line 1, in ?
  File "C:\PYTHON23\lib\site-packages\SQLObject\SQLObject.py", line 407,
in __new__
    val._init(id, connection, selectResults)
  File "C:\PYTHON23\lib\site-packages\SQLObject\SQLObject.py", line 667,
in _init
    selectResults = self._connection._SO_selectOne(self, dbNames)
  File "c:\python23\Lib\site-packages\SQLObject\DBConnection.py", line
306, in _SO_selectOne
    return self.queryOne("SELECT %s FROM %s WHERE %s = %s" %
  File "c:\python23\Lib\site-packages\SQLObject\DBConnection.py", line
149, in queryOne
    return self._runWithConnection(self._queryOne, s)
  File "c:\python23\Lib\site-packages\SQLObject\DBConnection.py", line
72, in _runWithConnection
    val = meth(conn, *args)
  File "c:\python23\Lib\site-packages\SQLObject\DBConnection.py", line
142, in _queryOne
    c.execute(s)
  File "C:\PYTHON23\Lib\site-packages\MySQLdb\cursors.py", line 95, in
execute
    return self._execute(query, args)
  File "C:\PYTHON23\Lib\site-packages\MySQLdb\cursors.py", line 114, in
_execute
    self.errorhandler(self, exc, value)
  File "C:\PYTHON23\Lib\site-packages\MySQLdb\connections.py", line 33,
in defaulterrorhandler
    raise errorclass, errorvalue
''' _mysql_exceptions.OperationalError : (2013, 'Lost connection to MySQL
server during query') '''
>>> Users(1)
<Users 1 username='bob' role='csr' fullname='Bob Linklater'
password='48181acd22b3edaebc8a447868a7df7ce629920a'>
>>>

= Chris Gahan =============
  (ch...@il...)

Re: [SQLObject] proxy attributes

[alexander s. <al...@an...>]

jws...@ra... wrote, at 17.02.2004 21:26:

>>(i do not think that you mean "account is a man" inheritance, do you?)
> 
> Actually, I do.

oh, please don't!  using inheritance ("is-a" relationship) instead of
client ("has-a") relationship between classes is highly error-prone and
often difficult to refactor later.

probably the best paper i've seen on the matter is chapter "Using
inheritance well" of the "Object-Oriented Software Construction" book by
Bertrand Meyer.  this chapter may be downloaded from
http://archive.eiffel.com/doc/manuals/technology/oosc/acrobat.html

however, SQLObject inheritance won't work in your case anyway: neither
is it designed for legacy databases nor can it handle many-to-one
relationships.

> Since python has multiple inheritance, you can see a parallel between joins
> and inheritance. Think of it like a mixin class. Not "account IS a man", 
> account has the properties of a man.

this may mean that both account and man *have* same contact info, or
that account *has* a contact person...  but as far as i understand, you
cannot amend the database structure, so these considerations do not
apply.  in your case, account has a buyer, and you cannot change that.

best wishes,
alex.

RE: [SQLObject] proxy attributes

[<jws...@ra...>]

>> No, customers = accounts and buyers = people. Inheritance is one way of 
>> looking at it, though. I will investigate that.
>
>perhaps not.  (i do not think that you mean "account is a man" 
>inheritance, do you?)  in our application, we had an object-oriented 

Actually, I do.

Since python has multiple inheritance, you can see a parallel between joins
and inheritance. Think of it like a mixin class. Not "account IS a man", 
account has the properties of a man. Since we are talking about databases,
these are attribute-only classes. No methods are inherited, since none 
exist.

In the two parallel universes-

The account table joins to the buyers table, and the buyers attributes are 
accquired by the account.

An account object inherits from the buyer class and merges the buyer 
attributes into itself.

I will investigate the inheritance code. Perhaps that would more appropriate
if there is interest in that approach.  

Re: [SQLObject] proxy attributes

[alexander s. <al...@an...>]

jws...@ra... wrote, at 17.02.2004 19:33:

>>>Note that from the standpoint of the cust_account object, these are all
>>>one-to-one relations.
> 
>>if the relations are strict one-to-one, then why data is divided into 
>>separate tables?
> 
> They are actually many-to-one, but from this side of the join it looks like
> a one-to-one. This customer account has that one buyer. From the buyer side
> of the join, that one buyer has many potential customer accounts.

well, this is a sketch of how we did attribute delegation before 
switching to Daniel's patch:

     class Buyer(SQLObject):
         whatever =Col()

     class MetaAccount(MetaSQLObject):
         def __new__(cls, name, bases, cdict):
             cdict["buyer"] = ForeignKey("buyer")
             return super(MetaAccount, cls).__new__(cls, name,
                 bases, cdict)

         def __init__(cls, name, bases, cdict):
             cls._buyer_attrs = ["whatever"]
             super(MetaAccount, cls).__init__(name, bases, cdict)
             for _attr in cls._buyer_attrs:
                 def _get(self, attr=_attr):
                     return getattr(self.buyer, attr)
                 def _set(self, value, attr=_attr):
                     setattr(self.buyer, attr, value)
                 setattr(cls, _attr, property(_get, _set))
             cls._reprItems = lambda s: s.buyer._reprItems() \
                 + super(s.__class__, s)._reprItems()

     class Account(SQLObject):
         __metaclass__ = MetaAccount

... and if you want Account.new() to update (or create) the buyer, you 
need some more magic to tweak classmethod `new` as well.

please note that this code is stripped down as much as possible and is 
not tested, but you can get the idea.

however, there were some problems with this delegation, discovered by 
Oleg Broytmann: as far as i understand, account.whatever cannot be 
accessed unless at least one instance of Buyer is created.  see 
http://sourceforge.net/mailarchive/forum.php?thread_id=3879735&forum_id=30269

>>perhaps relationships may be optional, e.g. some customers are buyers, 
>>and others are not?  if this is the case, then you may consider using 
>>SQLObject inheritance patch by Daniel Savard.
> 
> No, customers = accounts and buyers = people. Inheritance is one way of 
> looking at it, though. I will investigate that.

perhaps not.  (i do not think that you mean "account is a man" 
inheritance, do you?)  in our application, we had an object-oriented 
information model, class inheritance was there from the outset.

best wishes,
alex.

RE: [SQLObject] proxy attributes

[<jws...@ra...>]

>That seems much more doable.  You'd still have to do various traversals. 
>  Like, if you have a one-to-many relationship between A and B, and B 
>has an attribute "email", then you have to do a.b.email, instead of 
>simply a.email -- that's a separate feature entirely, and seems 

My immediate itch is many-to-one. I had not thought much about the
one-to-many case. I don't see any reason the existing scheme of returning
a list of objects would not work for me. It's hard to for me to 
evaluate my specific issue AND try to consider the One Correct Way.
I also labor under the disadvantage of near-complete ignorance of the
magic of metaclass programming. Enthusiasm and ignorance - A winning
combination!  

>Oh, and you have to fix _fromDatabase so that it detects foreign keys, 
>and can find classes based on table name.  But altogether a fairly 
>doable plan.

This is beginning to sound like traversal in Zope.

Re: [SQLObject] proxy attributes

[Ian B. <ia...@co...>]

jws...@ra... wrote:
> I should clarify- I, as the app do not care about the lesser tables. 
> I, as the programmer want to do as little typing as possible to 
> accomplish the task. The schema is the map and all the necessary 
> information is in there to build the classes automatically. When I
> define my classes, I would like to be able to say Here are my object
> names, here are my root tables, go build it. If I could get by with
> only defining my public(app-facing)classes and letting SO build
> whatever private(internal) classes it needs, I would be ecstatic, but
> that's likely hoping for too much.

Well, from what you're saying, all you really want is something that 
finds all the tables and creates classes from scratch.  Like 
_fromDatabase, only actually creating all the classes.  (I also think 
_fromDatabase isn't so good at foreign key identification, but that's 
fixable, at least for Postgres)

That seems much more doable.  You'd still have to do various traversals. 
  Like, if you have a one-to-many relationship between A and B, and B 
has an attribute "email", then you have to do a.b.email, instead of 
simply a.email -- that's a separate feature entirely, and seems 
particularly difficult to solve while also doing automatic class 
generation. (Probably not impossible, maybe not even difficult, but not 
where you want to start)

Anyway, Maxwell Hammer had a note about automatic class generation a 
couple days ago.  Expanding tableExists to find all tables is easy 
enough.  You'd probably want to run this automatic class generation 
after you instantiate your Smart Classes (i.e., ones that have actual 
business logic), and then that would fill in all the missing tables.

Oh, and you have to fix _fromDatabase so that it detects foreign keys, 
and can find classes based on table name.  But altogether a fairly 
doable plan.

   Ian

RE: [SQLObject] proxy attributes

[<jws...@ra...>]

>I think you may have an issue here. What benefits are you getting from
>SQLObject if you don't model those 'private' tables? I have similar
database
>setups (presenting multiple tables as unified objects), and model the
entire
>implementation. When you say you don't want to create objects for all
tables,
>I'm thinking you don't want your implementation-specific objects to be part
of
>your public interface, but I don't think you're going to get the ease of
>implementing these proxies in SQLObject without having private objects for
>every relevant table.

I should clarify- I, as the app do not care about the lesser tables. I, as
the
programmer want to do as little typing as possible to accomplish the task.
The
schema is the map and all the necessary information is in there to build the

classes automatically. When I define my classes, I would like to be able to
say
Here are my object names, here are my root tables, go build it. If I could
get
by with only defining my public(app-facing)classes and letting SO build
whatever
private(internal) classes it needs, I would be ecstatic, but that's likely
hoping
for too much.

In my specific case, there is a lot of structure in the database schema that
I
would rather not repeat by manual transcription into my classes. The scope
of
SO also includes less-featureful backends and the requirement of handling
legacy data stores in various degrees of normalization. If it can be derived
automatically, that saves me the burden of code synchronization and reduces
the 
chance of errors. If no exploitable schema exists, we can fall back to
asking
for specifications. 

RE: [SQLObject] proxy attributes

[<jws...@ra...>]

>least, if you never want a distinct object representation of one table 
>vs. the other.  Though in that case, the utility of the separate tables 
>is questionable -- is it really one-to-zero-or-one?  Well, then the 

I don't really care about the extra tables. They will initalized once at 
install-time and from then on the app will be handling only the top level
objects. The app will think the email address is a property of the 
customer account, but it is really stored as a attribute of the buyer, 
which is a foreign key from the customer account. I want to present an 
simple object interface on one side and a correct relational storage
on the other side. Creating that interface is rather tedious. The 
ability of SQLObject to reverse engineer the schema is rather compelling
and would factor out huge amounts of finger-grinding code. The value 
proposition is to bury all the complexity inside the SO classes. If I
compromise the relational schema to impedance-match the application, I'll
paint myself into a functionality corner eventually. 
 
>And since you are using Postgres, views are another possibility.  I 
>don't much care for them, but they seem like a good fit to what you're 
>doing.

Possibly, but this could fit into SO, and then could be usable on various 
back-ends. Are writeable views available on all modern versions of Postgres?

RE: [SQLObject] proxy attributes

[<jws...@ra...>]

>> Note that from the standpoint of the cust_account object, these are all
>> one-to-one relations.

>if the relations are strict one-to-one, then why data is divided into 
>separate tables?

They are actually many-to-one, but from this side of the join it looks like
a one-to-one. This customer account has that one buyer. From the buyer side
of the join, that one buyer has many potential customer accounts.

>perhaps relationships may be optional, e.g. some customers are buyers, 
>and others are not?  if this is the case, then you may consider using 
>SQLObject inheritance patch by Daniel Savard.

No, customers = accounts and buyers = people. Inheritance is one way of 
looking at it, though. I will investigate that.

Re: [SQLObject] proxy attributes

[Luke O. <lu...@me...>]

> cust_account   - main table
> buyer 	   - contact name, relates to customer account
> buyer_info     - contact address info, relates to buyer
> county  	   - relates to customer
> shipping_rates - relates to customer
>
> Note that from the standpoint of the cust_account object, these are all
> one-to-one relations. It should be relatively simple to traverse these
> relations and present the attributes of the sub-tables as attributes of the
> root class. I will call these 'proxy attributes'. If x is an instance of our
> cust_account class, I should be able to do 'print x.email_addr' and get the
> address of the buyer back. Obviously this only works if there is one
> traversal path across the tables- one-to-one relationships. Exposing those
> proxy attributes as properties of the root object is in the object-oriented
> spirit of 'present the interface, hide the implementation'. There would need
> to be some way of handling duplicate column names. We could either mandate
> unique column names or provide a way to use aliases.

Yes, so long as these are all one-to-one, it makes sense. I don't think there's
a need for worrying about unique column names, that's something you need to
resolve at your main object interface level. Any automated aliasing scheme
would seem to expose implementation in awkward ways. But I could be wrong.

> A conceptual difference is that I do not ever intend to create classes for
> minor tables. There will be objects for all the major components like
> employees and customers, and there will be objects like invoices that have a
> master-detail structure that will be exposed from attributes like
> 'line_items'. I will not be joining SQLObjects directly.

I think you may have an issue here. What benefits are you getting from
SQLObject if you don't model those 'private' tables? I have similar database
setups (presenting multiple tables as unified objects), and model the entire
implementation. When you say you don't want to create objects for all tables,
I'm thinking you don't want your implementation-specific objects to be part of
your public interface, but I don't think you're going to get the ease of
implementing these proxies in SQLObject without having private objects for
every relevant table.

class CustomerAccount(SQLObject):
    # private composition
    _buyer = ForeignKey('Buyer')

    # public proxy
    def _get_emailAddr(self):
        # this could also have proxy at Buyer obj to buyerInfo, so like:
        # return self._buyer.emailAddr ==
        return self._buyer.buyerInfo.emailAddr


makes this work:

x = CustomerAccount(id)
print x.emailAddr

Now, we could probably make this Proxy/Composition concept more automatic,
whether with nested classes or a Col-like object with name->proxyCol mappings:

class CustomerAccount(SQLObject):
    # nested
    class Buyer(SOProxy):
        _proxyClass = 'Buyer' # defaults from class name?
        # with second-level proxy as above
        emailAddr = 'emailAddr'
        # or
        emailAddr = 'buyerInfo.emailAddr'

    # col-like, have to be smart about multiple proxies to same class.
    emailAddr = Proxy('Buyer', 'buyerInfo.emailAddr')


Does this fit into your goals? No matter what, I don't see how you're going to
get around your middle layer explicitly describing these mappings from the
database implementation to your chosen public object interface.

- Luke

Re: [SQLObject] proxy attributes

[Ian B. <ia...@co...>]

jws...@ra... wrote:
> A conceptual difference is that I do not ever intend to create classes for
> minor tables. There will be objects for all the major components like
> employees and customers, and there will be objects like invoices that have a
> master-detail structure that will be exposed from attributes like
> 'line_items'. I will not be joining SQLObjects directly.

For tables that will have no object representation, a join is probably 
the best idea -- RelatedJoin (many-to-many) is an example of this.

For the one-to-one joins, I'm not really sure what would be best.  In a 
way, it could almost be phrased like having a table named "table1, 
table2", and appending "table1.id=table2.id" to all your queries.  At 
least, if you never want a distinct object representation of one table 
vs. the other.  Though in that case, the utility of the separate tables 
is questionable -- is it really one-to-zero-or-one?  Well, then the 
table name is "table1 left join table2 on table1.id=table2.id", with 
column names that include the table reference -- even easier!  All very 
hackish though.

And since you are using Postgres, views are another possibility.  I 
don't much care for them, but they seem like a good fit to what you're 
doing.

   Ian

Re: [SQLObject] proxy attributes

[alexander s. <al...@an...>]

jws...@ra... wrote, at 17.02.2004 17:46:

> The storage will be factored relentlessly, which means lots of small,
> specialized tables. I would like to use SQLObject to wrap up(join) those
> tables into convenient objects that can be manipulated. As an example, we
> want to have a customer account object in our app. That would be stored in
> the database as a number of tables-
> 
> cust_account   - main table
> buyer 	   - contact name, relates to customer account
> buyer_info     - contact address info, relates to buyer 
> county  	   - relates to customer
> shipping_rates - relates to customer
> 
> Note that from the standpoint of the cust_account object, these are all
> one-to-one relations.

if the relations are strict one-to-one, then why data is divided into 
separate tables?

perhaps relationships may be optional, e.g. some customers are buyers, 
and others are not?  if this is the case, then you may consider using 
SQLObject inheritance patch by Daniel Savard.

> It should be relatively simple to traverse these
> relations and present the attributes of the sub-tables as attributes of the
> root class. I will call these 'proxy attributes'. If x is an instance of our
> cust_account class, I should be able to do 'print x.email_addr' and get the
> address of the buyer back. Obviously this only works if there is one
> traversal path across the tables- one-to-one relationships.

we did attribute delegation like this, but it did not play well, so we 
recently switched to class hierarchies.  thanks Daniel!

best wishes,
alex.

[SQLObject] proxy attributes

[<jws...@ra...>]

Having now worked with SQLOject a bit, I have a better idea of how I intend
to use it and it's usefulness to my task. It appears that the fit is not
complete, but I think that what I want is within the scope of what SQLObject
aspires to be.

I am working on a 3-tiered application with a relational storage at the
bottom(Postgres), Business objects in the middle(SQLObject), and a
presentation layer to be determined.

The storage will be factored relentlessly, which means lots of small,
specialized tables. I would like to use SQLObject to wrap up(join) those
tables into convenient objects that can be manipulated. As an example, we
want to have a customer account object in our app. That would be stored in
the database as a number of tables-

cust_account   - main table
buyer 	   - contact name, relates to customer account
buyer_info     - contact address info, relates to buyer 
county  	   - relates to customer
shipping_rates - relates to customer

Note that from the standpoint of the cust_account object, these are all
one-to-one relations. It should be relatively simple to traverse these
relations and present the attributes of the sub-tables as attributes of the
root class. I will call these 'proxy attributes'. If x is an instance of our
cust_account class, I should be able to do 'print x.email_addr' and get the
address of the buyer back. Obviously this only works if there is one
traversal path across the tables- one-to-one relationships. Exposing those
proxy attributes as properties of the root object is in the object-oriented
spirit of 'present the interface, hide the implementation'. There would need
to be some way of handling duplicate column names. We could either mandate
unique column names or provide a way to use aliases. 

I can imagine either reverse-engineering the database at compile-time with
_fromDatabase or explicitly defining attributes and traversal paths. 

A conceptual difference is that I do not ever intend to create classes for
minor tables. There will be objects for all the major components like
employees and customers, and there will be objects like invoices that have a
master-detail structure that will be exposed from attributes like
'line_items'. I will not be joining SQLObjects directly.


Please comment.