sqlobject-discuss — All things SQLObject-related

[SQLObject] New style classes and __init__ vs _init

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

In the SQLObject docs, Ian writes:

"With new-style classes, __init__ is called everytime the class is 
called. That means it's called when an object is just fetched from the 
cache"

This was a bit surprising to me, so I Googled to find discussion of 
this, but came up empty. Can anybody point me to a discussion of this?

...Edmund.

Re: [SQLObject] two postgres questions

[Magnus <ma...@th...>]

At 15:24 2003-06-02 +0200, Bud P. Bruegger wrote:
>Agreed.  I tend to forget about pre-existing schemas since I decided
>I don't have a need for this myself.

You probably will one day...

>Well, I understand you philosophically, but what do you mean by object
>identity?

I mean that the object represents something unique in the
problem domain, or in the technical solution. This would
probably also be a design class that I would model in UML
if I choose to use UML. Instances of classes in the code
might not be objects with a unique (logical) identity, for
instance an integer instance in Smalltalk. My idea is that
there should be a one-to-one mapping between this kind of
object identity, and IDs in the database.

Philosophically I mean that there is a distinction between
an object, such as a person or a GUI windows, and a simple
value such as 42 meters or 3612 seconds. Martin Fowler calls
the latter "Value Objects" in his new book. Often, a value
object is simply a scalar like a string or an integer, but it
might certainly be something that won't fit in a simple scalar.

But what I'm after technincally is to map the kind of Python
classes I typically work with in a simple way. Uniform Python
lists and dictionaries are certainly common as attributes. So,
unless I am to contrieve my python object model, I have to map
a logic layer class to several SQL tables.

I guess you could say that if it makes sense to have a foreign
key that links with an object, it *can't* be a value object. I'm
not philosophically certain that this is right, but it certainly
seems reasonable, that anything that we would like to specifically
point out from another table would be more than a mere value. It's
not anonymous any more...

>Yes, I also see these two possibilities.  You haven't been explicit
>about how this relates to GUID or other primary key, so I state is
>more explicitly:
>
>Tables that manage values (as opposed to objects) don't necessarily
>need an OID as shadow information.  Your example is convincing; the
>key of the dictionary is a good solution for a primary key.

Together with the oid of the main object, yes. Like this:

class X so that x = X(), x.a = ['brown, 'green'], x.b = {'a': 42, 'b': 17}
x.c = 'Tree', x.id = 123, I would have

Table x
|id  | c  |
|123 |Tree|

Table x_a
|x_id|index|value|
|123 |0    |brown|
|123 |1    |green|

Table x_b
|x_id|key|value|
|123 |a  |42   |
|123 |b  |17   |

I never want to have a situation where a foreign key is anything
but a single oid field though. If I want to have a foreign key to
a row in a "value table", I would elevate the valur to an "full"
object. This is also aligned with normal object oriented modeling
in UML etc. I can't model a relationship to a plain value in UML.
I can only have a relationshiop with an instance of a class.

If we acually implement these attributes as simple lists and
dictionaries in Python, there will no simple way to keep track
of any ID-fields in the x_a or x_b tables anyway. It seems they
would just be dead weight.

Let's say we do, x.a[1] = 'black', x.a.sort(), and then we want to
save the instance. Now we should have

Table x_a
|x_id|index|value|
|123 |0    |black|
|123 |1    |brown|

I guess I'd just do "DELETE FROM x_a WHERE x_id = 123" and INSERT
the current values. If I had had something like...

Table x_a
| id|x_id|index|value|
|101|123 |0    |brown|
|102|123 |1    |green|

I can't see how this would have helped me... The simple python
list object can't be kept in sync with those id fields anyway.
This id field would just be dead weight, and I'd have to run
the DELETE/INSERT anyway. Right?

>I'm not overly concerned with additional overhead introduced by a ORM
>middleware layer, so I could probably live with an unnecessary GUID.

But it seems it would be dead weight in this case.

>But thinking aloud, maybe there is some systematics to it since also
>breakup tables used in many:many relationships don't need guids...

Right. This is something similar.

>Hmmm.  just an intuition but I haven't understood the systematics of
>it all the way...

There are many ways to skin a cat... :)

I do tend to feel that my logic objects should have an SQLObject
derivate as an attribute, rather than to inherit DQLObject themselves.
I.e. my business objects use some kind of storage objects, they aren't
storage objects. But I might skip this for very small systems.

How do other SQLObject users handle this?

>I had a paper in mind that wasn't listed there, only on OIDs..  But
>thanks for the links..

It's probably among the others at AmbySoft though.


--
Magnus Lycka (It's really Lyckå), ma...@th...
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The shortest path from thought to working program 

Re: [SQLObject] two postgres questions

[Bud P. B. <bu...@si...>]

On Mon, 02 Jun 2003 12:46:44 +0200
Magnus Lyckå <ma...@th...> wrote:

> At 12:09 2003-06-02 +0200, Bud P. Bruegger wrote:
> >Magnus,
> >
> >I very much agree with your approach to primary keys.  This is why I
> >suggested to use GUID or UUID in SQLObject some time ago and voted
> >against the possibility to use multi-column primary (and foreign)
> >keys.
> 
> On the other hand, I can understand the need to intergrate with
> Legacy databases, so allowing multi-column keys could certainly
> be useful for *that*.

Agreed.  I tend to forget about pre-existing schemas since I decided
I don't have a need for this myself.


> Another issue to consider is that SQL tables are much more limited
> than Python classes. If we have a class X with an attribute Y which
> is a dict containing string => string, that can obviously not be a
> column in table X, but on the other hand, we would not assign an object
> identity in Python for each dict element.

Well, I understand you philosophically, but what do you mean by object
identity?  Every instance in python has an OID which is basically it's
menory address...  ...and I don't believe anyone assigns OIDs in
python.  

> The most straight forward solution seems for me to be a table
> 
> X_Y with columns x_id int, key varchar(...), value varchar(...).
> 
> Now, it seems I neet to either obscure X.Y as a pickle etc, or to
> give each dict element an object identity. One makes the database
> less clear and searchable, the other makes the db much heavier than
> the pure Python implementation.

Yes, I also see these two possibilities.  You haven't been explicit
about how this relates to GUID or other primary key, so I state is
more explicitly:

Tables that manage values (as opposed to objects) don't necessarily
need an OID as shadow information.  Your example is convincing; the
key of the dictionary is a good solution for a primary key.  

I'm not overly concerned with additional overhead introduced by a ORM
middleware layer, so I could probably live with an unnecessary GUID.
But thinking aloud, maybe there is some systematics to it since also
breakup tables used in many:many relationships don't need guids...
Hmmm.  just an intuition but I haven't understood the systematics of
it all the way...


> >I believe I was originally convinced of this by a paper by Scott
> >Ambler--but I didn't find the refernece anymore..
> 
> Me too. They are in my wiki I think.
> http://www.thinkware.se/cgi-bin/thinki.cgi/DatabaseDesign

I had a paper in mind that wasn't listed there, only on OIDs..  But
thanks for the links..

--b

Re: [SQLObject] two postgres questions

[Magnus <ma...@th...>]

At 12:09 2003-06-02 +0200, Bud P. Bruegger wrote:
>Magnus,
>
>I very much agree with your approach to primary keys.  This is why I
>suggested to use GUID or UUID in SQLObject some time ago and voted
>against the possibility to use multi-column primary (and foreign)
>keys.

On the other hand, I can understand the need to intergrate with
Legacy databases, so allowing multi-column keys could certainly
be useful for *that*.

Another issue to consider is that SQL tables are much more limited
than Python classes. If we have a class X with an attribute Y which
is a dict containing string => string, that can obviously not be a
column in table X, but on the other hand, we would not assign an object
identity in Python for each dict element.

The most straight forward solution seems for me to be a table

X_Y with columns x_id int, key varchar(...), value varchar(...).

Now, it seems I neet to either obscure X.Y as a pickle etc, or to
give each dict element an object identity. One makes the database
less clear and searchable, the other makes the db much heavier than
the pure Python implementation.

>I believe I was originally convinced of this by a paper by Scott
>Ambler--but I didn't find the refernece anymore..

Me too. They are in my wiki I think.
http://www.thinkware.se/cgi-bin/thinki.cgi/DatabaseDesign


--
Magnus Lycka (It's really Lyckå), ma...@th...
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The shortest path from thought to working program 

Re: [SQLObject] two postgres questions

[Bud P. B. <bu...@si...>]

Magnus,  

I very much agree with your approach to primary keys.  This is why I
suggested to use GUID or UUID in SQLObject some time ago and voted
against the possibility to use multi-column primary (and foreign)
keys.  

In my prototype ORM, I use a guid implementation that I found in an
ASPN cookbook and it seems to work just fine.  

I believe I was originally convinced of this by a paper by Scott
Ambler--but I didn't find the refernece anymore..

-b

On Thu, 29 May 2003 03:56:30 +0200
Magnus Lyckå <ma...@th...> wrote:

> At 19:45 2003-05-28 -0400, Edmund Lian wrote:
> >To be honest, I'd rather see synthetic primary keys banished. But, while 
> >PostgreSQL allows changes in primary keys to cascade automatically to 
> >foreign keys in other tables, Oracle and a few other databases do not. So, 
> >the need to be portable might well necessitate synthetic keys.
> 
> Does *any* database but PostgreSQL support cascaded key updates
> like that?
> 
> Anyway, this is *not* the big problem with "natural" primary keys.
> 
> The main problem is that the business rules change, and making big
> changes in large, actively used databases is very disruptive and
> expensive. We want to isolate each needed change as much as possible.
> 
> It's far from unique that the set of columns that was the ideal key
> yesterday isn't that any longer. Yesterday it was a solid business
> rule that only one row in this table could be created per person
> each day, so personId + registryDate seemed to be the ideal combo.
> The business experts are so sure that they would bet their arm on it.
> Today things changed, and in a few cases, it might actually happen
> that there have to be two rows for one person on certain dates.
> 
> With synthetic keys, this means that we drop a unique index. With
> natural keys, our primary key breaks, and with that maybe a whole
> tree of keys for detail and sub-detail tables that use these
> fields as parts of their primary keys. Yuk! Been there, done that...
> I solved it ugly, and cheated with the date in those rare cases,
> using the next day, if the current was used, but I'd rather do
> things cleanly.
> 
> Using the primary key of one table as part of the primary key for
> another table just because there is a parent-child relationship
> between those tables mean that we have a much tighter coupling
> between the tables than we have with synthetic keys. While this
> evil, tight coupling can sometimes be avoided in the *primary*
> keys of dependent tables, we can't avoid having the entire
> primary key (which contains business information for another table)
> as foreign key fields in the dependent table.
> 
> Not only the data model is hampered by this. The classes in the
> application logic, whether it's implemented in Python, Java or
> C++ will carry along attributes that really belong to another
> class! This also means that the cost of implementing a business
> rule change is multiplied.
> 
> By using a uniform primary key type, we will also always have a
> uniform foreign key type, and it will be much easier to change
> the table structure. After all we sometimes realize the the X
> objects aren't really atttibutes of the Y object, but rather of
> the Z objects. Natural keys create some kind of software structure
> cement, and makes changes in business rules very hard. Often,
> these structures get cemented long before the product is even
> launched. It's one thing that it's difficult to migrate loads of
> important business data, but it *should* at least be swift to
> change the system if it isn't running yet.
> 
> For a different problem that brought me to the same conclusion, see
> http://www.thinkware.se/cgi-bin/thinki.cgi/CaseStudyUnbrokenSeries
> 
> 
> 
> --
> Magnus Lycka (It's really Lyck&aring;), ma...@th...
> Thinkware AB, Sweden, www.thinkware.se
> I code Python ~ The shortest path from thought to working program 
> 
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: eBay
> Get office equipment for less on eBay!
> http://adfarm.mediaplex.com/ad/ck/711-11697-6916-5
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> 


/-----------------------------------------------------------------
| Bud P. Bruegger, Ph.D. 
| Sistema (www.sistema.it)
| Via U. Bassi, 54
| 58100 Grosseto, Italy
| +39-0564-411682 (voice and fax)
\-----------------------------------------------------------------

Re: [SQLObject] Re: Impressions so far

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

Bud P.Bruegger wrote:

> * Looking at other ORMs, I think we can't give enough credit to Ian
>   for having found a way of doing things EASY and intuitive.  Some of
>   that stuff looks to complex and verbose that it would be great for
>   Enterprise Java...  Thanks, Ian!  One really starts to appreciate
>   this more and more when one looks at alternatives  (which I haven't
>   done much before..)

Yes, I do agree... I'm exercising SQLObject (and myself) quite a bit, 
and it is proving to be very pleasant to use. Thank you Ian!

I've been making a lot of comments, not because I'm complaining, but 
because I think SQLObject has a good chance at being the best Python ORM 
  out there. I find that Ian's philosophy and desire for clean 
interfaces (e.g., the choice to use attribute style access for column 
data) to be dead-on.

...Edmund.

Re: [SQLObject] Re: Impressions so far

[Bud P. B. <bu...@si...>]

Apologies for falling behind the discussion (and it's probably going
to get worth starting from tomorrow).  Looked at Modeling too and have
two impressions:

* Looking at other ORMs, I think we can't give enough credit to Ian
  for having found a way of doing things EASY and intuitive.  Some of
  that stuff looks to complex and verbose that it would be great for
  Enterprise Java...  Thanks, Ian!  One really starts to appreciate
  this more and more when one looks at alternatives  (which I haven't
  done much before..)

* Apart from that, I kind of like Modelings simple dot-notation for
  specifying Where conditions on related objects.  Will have to look
  into this more..

--bud


On 30 May 2003 21:48:21 -0500
Ian Bicking <ia...@co...> wrote:

> > 14. Modeling (which seems rather hard to get into) seems to have very 
> > expressive ways to get complex joins. I haven't tried it at all, but 
> > have browsed the docs. Might be worth stealing some ideas from them. But 
> > gee, theyReallyLikeLongMethodNames! Or maybe I'm sensitive to them due 
> > to RSI...
> 
> I've only looked at Modeling a bit.  But ouch... those functions and
> methods are painful to read, not to mention write.  Like:
> 
>             from Modeling.FetchSpecification import FetchSpecification
>             from Modeling.Qualifier import qualifierWithQualifierFormat
>         
>             qualifier=qualifierWithQualifierFormat('lastName=="Hugo"')
>             fetchSpec=FetchSpecification(entityName='Writer',
>             qualifier=qualifier)
>             objects=ec.objectsWithFetchSpecification(fetchSpec)
>         
> That's a "simple" fetch.  Hurts my head just having that on the page.  Oh my:
> 
>         aBook.addObjectToBothSidesOfRelationshipWithKey(anAuthor, 'author')

/-----------------------------------------------------------------
| Bud P. Bruegger, Ph.D. 
| Sistema (www.sistema.it)
| Via U. Bassi, 54
| 58100 Grosseto, Italy
| +39-0564-411682 (voice and fax)
\-----------------------------------------------------------------

Re: [SQLObject] How to store attributes of relationships?

[Nick <ni...@dd...>]

Edmund Lian wrote:
> There is one thing though--without specifying a unique (person_id, 
> role_id) constraint, it is possible to insert duplicate mappings into 
> the table. Adding the constraint stops the redundent entry, but causes 
> SQLObj to raise an exception (because psycopg raises an exception). I 
> think the exception should be caught and silently ignored.

Actually it would be nice if psycopg actually raised meaningful exceptions 
rather than OperationalError for every damn thing.

Nick

Re: [SQLObject] How to store attributes of relationships?

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

I did an experiment this morning to see if the mapping table could be 
treated as a first class table. It can, so I don't think SQLObject needs 
to be modified. Here's what I did:

class Person(SQLObject):
     _columns = [
         StringCol("firstName"),
         StringCol("middleInitial", default=None),
         StringCol("lastName")]
     _joins = [RelatedJoin("Role")]

class PersonRole(SQLObject):
     _columns = [
         IntCol("personId"),
         IntCol("roleId"),
         StringCol("color"),
         StringCol("weight")]

class Role(SQLObject):
     _columns = [
         StringCol("name")]
     _joins = [RelatedJoin("Person")]


Rather than rely on auto schema generation, I just created the mapping 
table myself, and included an id column as well as test attribute 
columns. With this, the many-to-many mapping via .addRole, .addPerson 
etc. works. And, you can create a PersonRole object directly and 
manipulate its attributes as:

pr = PersonRole.new(...)
pr.color = "Blah"


There is one thing though--without specifying a unique (person_id, 
role_id) constraint, it is possible to insert duplicate mappings into 
the table. Adding the constraint stops the redundent entry, but causes 
SQLObj to raise an exception (because psycopg raises an exception). I 
think the exception should be caught and silently ignored.


...Edmund.

Re: [SQLObject] How to store attributes of relationships?

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

Luke Opperman wrote:

> Having written this up, I can't think of how I'd really clean this up to be
> more directly supported... Thoughts? The RelatedJoin shortcut might be able to
> be cleared up, to be more of a generic multiple-level-join shortcut thing, but
> other than that... I guess I kind of like it.

I think the best way to handle this is to always make the 
mapping/intersection table a first class table/object, with a serial id 
column, etc. Kind of like what you did. Right now, it lurks around, 
buried in RelatedJoin declarations, does not have a serial id, etc.

If it were a distinct, accessible object then users could add other 
tables/objects with foreign keys pointing to the relevant mapping.

...Edmund.

[SQLObject] Namespace pollution

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

The way SO does things is quite nice. I do like the use of metaclasses 
to add in new methods as a result of joins. But, there needs to be a way 
to inspect the objects to see what methods and attributes have been 
automagically added, I think.

I'm just speculating--because I haven't had the problem yet--that it 
could be quite easy to accidentally over-ride an automagically added 
method/attribute. Particularly if you've got a lot of classes, or wrote 
them a long time ago, or somebody else wrote them. In which case, it 
isn't at all obvious the methods/attributes of each object are.

...Edmund.

Re: [SQLObject] How to store attributes of relationships?

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

Well, I have to admit to dealing with this problem, in not very nice ways, and
not trying to find a better solution for SO yet. So this idea is not directly
supported, but...

First, I define an SO class for the intersection, DogPerson or something more
meaningful like DogOwners. Whatever. And define it as though it has
foreignKeys to Dog and Person, and whatever other attrs.

Then Person and Dog each have MultipleJoins to this class. aDog.owners or
aPerson.ownedDogs gives access to the intersection objects. Each main class
also gets a RelatedJoin to the other. This just facilitates shortcut access
(aDog.persons == [ x.person for x in aDog.owners ] ).

I think that about covers it. Something makes me think there are also cases
where I've done a version of joinWhere (aDog.ownersWhere('otherAttr = x')),
but I can't think of why now, and it probably was due to the more general
desire for joinWhere than anything to do with intersection records.

Having written this up, I can't think of how I'd really clean this up to be
more directly supported... Thoughts? The RelatedJoin shortcut might be able to
be cleared up, to be more of a generic multiple-level-join shortcut thing, but
other than that... I guess I kind of like it.

- Luke

P.S. joinWhere... just a combination of select and join behaviors, but accessed
more like a parameterized join. I ought to hurry up and make this code more
generic, as I think other people have asked for similar functionality..

[SQLObject] How to store attributes of relationships?

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

It's boring me again. I've come across a conundrum... how to store 
attributes of a relationship. Here's the problem...

Suppose you have two entities: Person and Dog.

You want to be able to model the fact that there is a relationship 
between any person and any dog. The way it would be done with SQLObject, 
I suppose, would be to use RelatedJoin as per:

class Person(SQLObject):
     _columns = [
         "name"]
     _joins = [RelatedJoin("Dog")]

class Dog(SQLObject):
     _columns = [
         "name"]
     _joins = [RelatedJoin("Person")]


When RelatedJoins are used, the relationship is stored as a row in a 
mapping table named Dog_Person. So far so good, this is how it is done 
in a traditional schema too.

Now the problem is that relationships have attributes too. For example, 
we might want to store the date relationship started, intensity, etc. 
These are attributes of the relationship, not of Person or Dog.

In traditional schema, this would be stored in the mapping table, or 
some table that is related to the mapping table. How do we deal with 
this in SQLObject?

...Edmund.

Re: [SQLObject] Re: Impressions so far

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

Luke Opperman wrote:

> Actually, notNull and unique are both currently supported by columns. Ian's a
> good documenter, there's just been a lot of changes since the last cut. :)

I stumbled across the unique option about the same time as you were 
emailing me!

A suggestion: remove the notNull option, and force people to use 
notNone. The former is really a SQL thing, and the latter a Python 
thing. On the theory that people who use ORMs don't want to know about 
SQL, having notNull is a bit unnecessary.


> Yep. Big problem holding this back is that any current implementation of a
> BooleanCol doesn't work in Postgres, because saying "obj.boolCol = True" fails
> during SQL conversion, since PG won't accept integer literals for boolean
> values. Grr.. (I'm a happy PG user, but this always bothers me...)

Do a behind-the-scenes conversion to the strings "1" and "0" or "t" and 
"f". This way, boolean support in DBs that don't have booleans (e.g., 
Oracle) is easier.


> It specifies results that are neither Cars nor Persons. How would you propose
> to represent the return values from such a function as objects?
> 
> I think in the O-R world there is no way to do this without loops or Set
> operations. But I'd happily be proven wrong.

I see the problem. And yet, this is such a common thing to want to do... 
Hmmm... returning a list would be best, but these certainly aren't 
schema-derived objects... Hmmmm...


> We use a global pool too, so we have a simple subclass of PostgresConnection
> that imports the pool and overrides getConnection (return
> globalPool.getConnection()), releaseConnection (conn.close()), and
> makeConnection (a no-op). _connection (and DBConnection etc) have a confusing
> name, as they define a lot more than just the connection... they also do all
> the SQL access generation, so you can't just set it to your current pool's
> connections.

Hmmm... so if multiple SQLObject modules are imported, will they all use 
the same global pool? What if they have differentt connection strings?

Nuts, I guess I'll have to take a closer look at the DB connection 
code... I just want a single place to specify the connection string for 
all modules, and have them use the same pool.


BTW, I'm really banging away at SQLObject, and had to rewrite my data 
model to suit it. However, layering SQLObj on to it has been quite 
painless, and it does seem to be very pleasant to use. Being able to 
control the names of the underlying columns and tables is _really_ critical.

...Edmund.

Re: [SQLObject] Re: Impressions so far

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

Hey Ian,

I had a quick glance at Col.py, and it appears that you do support 
specification of uniqueness (as well as notNull, which you mentioned)...

...Edmund.

Re: [SQLObject] Re: Impressions so far

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

> True, however, if you're going to support schema generation, it makes
> sense to at least support the most common constraints: unique, not null,
> etc. As it stands, SQLObject nearly supports the common cases. Adding
> uniqueness support would allow SQLObject to support most common cases.

Actually, notNull and unique are both currently supported by columns. Ian's a
good documenter, there's just been a lot of changes since the last cut. :)

Note that uniqueness is implied by "alternateID" as well, which is what
generates the byColName-type acccesses... maybe unique and alternateID should
become aliases? (only downside I can see is extra method creation overhead if
you don't actually care to access byName...)

> Speaking of enums, I notice that there's no explicit support for
> booleans. No big deal unless you consider schema generation, where the
> boolean column type should be generated.

Yep. Big problem holding this back is that any current implementation of a
BooleanCol doesn't work in Postgres, because saying "obj.boolCol = True" fails
during SQL conversion, since PG won't accept integer literals for boolean
values. Grr.. (I'm a happy PG user, but this always bothers me...)

> If I don't use a left outer join, I will get a list of people who have
> cars, and the people who don't have cars won't appear in it.
...
> How do I achieve the same as a left outer join without writing a loop?

Hmm. I see your example, and from an object dispatch perspective I'm not sure
how to solve it. I'm less interested from a performance viewpoint (it doesn't
matter to me immediately whether SQLObject actually uses LEFT/RIGHT join SQL),
but how I would specify this in python other than a Set (list) operation... I
suppose something explicit like Car.unionPerson / Person.unionCar could
work... ahh, there's the reason this doesn't fit into SO's model:

It specifies results that are neither Cars nor Persons. How would you propose
to represent the return values from such a function as objects?

I think in the O-R world there is no way to do this without loops or Set
operations. But I'd happily be proven wrong.

> This raise the bigger question of how to handle connections when
> multiple modules, each implementing SQLObjects are used. What is the
> best way to do this?

Here's how we do it: Each group/registry defines a "CoreObject", an abstract
superclass for all actual objects that defines _connection. One place to
change it for an entire collection of objects.

> I have a global connection pool, so perhaps I should just have each
> SQLObject-based module import the pool and fetch a connection. Is this
> what the _connection attribute is for? Can we not use the DBConnection
> object?

We use a global pool too, so we have a simple subclass of PostgresConnection
that imports the pool and overrides getConnection (return
globalPool.getConnection()), releaseConnection (conn.close()), and
makeConnection (a no-op). _connection (and DBConnection etc) have a confusing
name, as they define a lot more than just the connection... they also do all
the SQL access generation, so you can't just set it to your current pool's
connections.

(Ian: thoughts on in a version or two renaming that to one of the other
possibilities like _dbDriver or ...?)

> Oh about orderBy... how do we specify whether it is ascending or descending?

You're the second (or third, if you count me :)) person who's asked this
recently.. not currently supported. It would need to be added to
SelectResults, and preferrably an addition to _defaultOrder would also be
made, although I didn't really like my initial naming _defaultOrderDirection,
but I can't think of a way to easily make it simply part of the _defaultOrder
var either (an optional tuple? urgh)..

Enough for now,

- Luke

[SQLObject] Re: Impressions so far

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

Ian Bicking wrote:


>>6. Need to have some way of specifying unique constraints for single and 
>>  groups of columns for auto schema generation. --- See (7) below.
> 
> 
> At some point SQLObject shouldn't do every detail of schema creation. 
> Maybe there should be a hook for adding your own SQL that gets executed
> on createTable/dropTable, so you can use those methods without (or in
> addition to) the automatic table definition.

True, however, if you're going to support schema generation, it makes
sense to at least support the most common constraints: unique, not null,
etc. As it stands, SQLObject nearly supports the common cases. Adding
uniqueness support would allow SQLObject to support most common cases.


>>7. I don't use enums at all. For table driven applications, I use 
>>primary keys coupled with foreign key constraints. This allows you to 
>>change the allowed values of a column by varying the contents of another 
>>table. This is one argument against using synthetic primary keys. But 
>>using synthetic primary keys is still OK so long as unique columns can 
>>be specified, and the foreign key constraint can take the name of a column.

Speaking of enums, I notice that there's no explicit support for
booleans. No big deal unless you consider schema generation, where the
boolean column type should be generated.


>>8. There is no way to specify (at least not that I can see), outer 
>>joins. These are important. ---
> 
> You'll have to give an example of what you want to do.  I don't see the
> need.

Maybe it is just that I don't know how to do it in SQLObject? Here's 
what I'm trying to do...

Schema:

create table person (
     id       serial,
     username varchar(30),
--
constraint person_key
     primary key (id),
constraint person_uq
     unique (username)
);

create table car (
     id       serial,
     owner_id integer,
     make     varchar(30),
--
constraint car_key
     primary key (id)
constraint car_owner_fk
     foreign key (owner_id) references person (id)
);


What I want: a list of car makes and owners. In SQL:

select p.username, c.make
from person as p
     left join car as c on p.id = c.owner


If I don't use a left outer join, I will get a list of people who have
cars, and the people who don't have cars won't appear in it.


In SQLObject:

class Person(SQLObject):
     _columns = [
         StringCol("username", length=30, notNone=True,
             alternateID=True)]

class Car(SQLObject):
     _columns = [
         StringCol("make", length=30, notNone=True),
         IntCol("ownerId", foreignKey="Person")]


To get a list of all people: Person.select()
To get a list of all car makes: Car.select()

How do I achieve the same as a left outer join without writing a loop?



> notNull=True or notNone=True as a keyword argument to your column.

OK, it wasn't in the manual, or I'm going blind.


> Yeah... but I'm still unsold.  You can always do:
> 
> class Person(SQLObject):
> 
>     def _get_gender(self):
>         return self._SO_get_gender().name
>     def _set_gender(self, value):
>         self._SO_set_gender(Gender.byName(value))
> 
> It's not that bad to use properties and create your own attributes, if
> SQLObject's attributes don't suite you.  People should do that more -- I
> went out of my way to make it possible (and to document it)!  This is
> exactly the sort of thing it's there for.

Sorry, I didn't understand the implications of this when I read the 
documentation. I'll give it a shot.


Regarding the _connection class attribute. How do I use it? Do I just 
pass it a connection object?

This raise the bigger question of how to handle connections when 
multiple modules, each implementing SQLObjects are used. What is the 
best way to do this?

Should I just import the connection string from a global settings file 
and have each module open its own connection via __connection__? Seems 
wrong since I might want to change the backend DB, and don't want to 
dive into each module to edit the __connection__ arguments.

I have a global connection pool, so perhaps I should just have each 
SQLObject-based module import the pool and fetch a connection. Is this 
what the _connection attribute is for? Can we not use the DBConnection 
object?

Oh about orderBy... how do we specify whether it is ascending or descending?


...Edmund.

Re: [SQLObject] Re: Bug, was: one to one relationship via foreign key--bug or dumbness?

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

On Sat, 2003-05-31 at 00:56, Nick wrote:
> Ian Bicking wrote:
> > categoryId is '3', not the integer 3.  Why is this happening?  That
> > seems to be the problem, but it shouldn't be possible to get a
> > non-integer in there.
> 
> As I recall, his original definition was a StringCol.

That would do it.  

I should just take foreignKey the keyword argument out.  Or, rather, it
could be used directly with Col (maybe using sqlType), or with KeyCol or
ForeignKey (ForeignKey being the prefered technique).

Well... really integer IDs aren't that essential.  The only time I've
really noticed it is with the %i in __repr__.  So maybe that could also
be relaxed.  Of course, in this case it actually was a bug, so...

  Ian

Re: [SQLObject] Re: Bug, was: one to one relationship via foreign key--bug or dumbness?

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

Nick wrote:

> Ian Bicking wrote:
> 
>> categoryId is '3', not the integer 3.  Why is this happening?  That
>> seems to be the problem, but it shouldn't be possible to get a
>> non-integer in there.
> 
> 
> As I recall, his original definition was a StringCol.

Yes it was. I could not just specify Col because the scheme could not 
then be autogenerated.

...Edmund.

Re: [SQLObject] Re: Bug, was: one to one relationship via foreign key--bug or dumbness?

[Nick <ni...@dd...>]

Ian Bicking wrote:
> categoryId is '3', not the integer 3.  Why is this happening?  That
> seems to be the problem, but it shouldn't be possible to get a
> non-integer in there.

As I recall, his original definition was a StringCol.

Nick

[SQLObject] Re: Bug, was: one to one relationship via foreign key--bug or dumbness?

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

On Fri, 2003-05-30 at 21:53, Edmund Lian wrote:
> Ian Bicking wrote:
> 
> > Okay, *this* time!  Finally wrote the dumb unit test...
> 
> Almost there!  :-) I have one failure:
> 
>  >>> from test import *
>  >>> cc = ComponentCategory.new(name="CAT")
>  >>> cc
> <ComponentCategory 3 name='CAT' description=None sequenceNum=None>
> 
> 
> Create component with default None for category:
> 
>  >>> c = Component.new(name="Mad")
>  >>> c
> <Component 9 name='Mad' categoryId=None sequenceNum=None>
>  >>> c.category
> 
> 
> 
> Try assigning instance of ComponentCategory to category attribute:
> 
>  >>> c.category = cc
>  >>> c.category
> <ComponentCategory 3 name='CAT' description=None sequenceNum=None>
> 
> 
> Now try creating component while passing in an instance of 
> ComponentCategory:
> 
> 
>  >>> c = Component.new(name="Mad", category=cc)
>  >>> c
> <Component 10 name='Mad' categoryId='3' sequenceNum=None>
>  >>> c.category
> Traceback (most recent call last):
>    File "<stdin>", line 1, in ?
>    File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 
> 928, in __repr__
>      ' '.join(['%s=%s' % (name, repr(value)) for name, value in 
> self._reprItems()]))
> TypeError: an integer is required

Something weird is going on here.  Note:  
<Component 10 name='Mad' categoryId='3' sequenceNum=None>

categoryId is '3', not the integer 3.  Why is this happening?  That
seems to be the problem, but it shouldn't be possible to get a
non-integer in there.  Unless cc was already messed up before it got
assigned, and its id was '3'.  Component('3') would very possibly
work... maybe there should be an assert somewhere to avoid this... I
assume that happened in some step you're not showing.

  Ian

[SQLObject] Re: Impressions so far

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

On Thu, 2003-05-29 at 23:33, Edmund Lian wrote:
> Ian,
> 
> I've been banging away at SQLObject all day, trying to jam a small 
> portion of my data model into it, or else rewrite it so it would fit. 
> Here are my thoughts so far, each worth $0.02, and in no particular order.
> 
> I have appended + and minus signs to indicate strength of feeling. More 
> of either = stronger positive or negative reation. +- = no reaction.
> 
> 1. Being able to autogenerate schema is nice, and this is how I've been 
> using testing SQLObject (SO). ++

Yes, very useful for testing, moreso than I expected.  Great for unit
testing.

> 2. Support for multiple DBs, particularly PostgreSQL, is good. +++
> 
> 3. There needs to be an object.delete() method that does not require you 
> to supply the object ID. When not supplied, it deletes the current 
> object. There is a bug in this right now. When you do 
> object.destroySelf(), the corresponding row is deleted from the DB, but 
> the instance data is not invalidated. +-

Invalidating instance data is annoying.  destroySelf is annoying.  It
all reeks of manual memory management, though I suppose such is life
with an RDBMS. 

> 4. Being able to supply a callable object for the default value of a 
> column is good. +++
> 
> 5. Being able to override table name is important because when you have 
> lots of tables floating around, you often want to control their names so 
> that you group them together is a specific way when the table names are 
> sorted. ++++
> 
> 6. Need to have some way of specifying unique constraints for single and 
>   groups of columns for auto schema generation. --- See (7) below.

At some point SQLObject shouldn't do every detail of schema creation. 
Maybe there should be a hook for adding your own SQL that gets executed
on createTable/dropTable, so you can use those methods without (or in
addition to) the automatic table definition.
 
> 7. I don't use enums at all. For table driven applications, I use 
> primary keys coupled with foreign key constraints. This allows you to 
> change the allowed values of a column by varying the contents of another 
> table. This is one argument against using synthetic primary keys. But 
> using synthetic primary keys is still OK so long as unique columns can 
> be specified, and the foreign key constraint can take the name of a column.
> 
> 8. There is no way to specify (at least not that I can see), outer 
> joins. These are important. ---

You'll have to give an example of what you want to do.  I don't see the
need.

> 9. Method of specifying joins seems a tad clumsy. Object Relational 
> Membrane's method is cleaner.

Yes, needing to declare both forward and backward references is a tad
awkward.  There may yet be advantages of joins being in both locations
-- for instance, being able to add ordering information.  But eh... it's
not perfect.

Though when I look at ORM, it's not that much different than what I'm
doing, and I even avoid the circular dependency problem.  The names may
be better in ORM -- I don't like my names.  But then one2many -- who is
the one, who is the many?  That's what I find hard in naming.

> 10. There needs to be a way to specify a not null constraint.

notNull=True or notNone=True as a keyword argument to your column.

> 11. As an extension of (7), it is clumsy to have to pass in an object 
> (A) when creating another object (B) that has a foreign key constraint 
> refering to a column in (A). Here's an example that assumes I can add 
> unique (6), not null constraints (10), and name a column for a foreign 
> key (7) to a column:
> 
> class Gender(SQLObject):
>      _columns = [
>          StringCol("name", unique=1, nullable=0)]

StringCol("name", alternateID=True)
# or with the new syntax:
name = StringCol(alternateID=True)

male = Gender.byName('male')

> class Person(SQLObject):
>      _columns = [
>          StringCol("name"),
>          StringCol("gender", nullable=0, foreignKey="Gender.name")]

Hmmm... that's not so bad.  But it does complicate the SQL a fair
amount, at least if it wasn't going to involve two SQL queries for doing
person.gender.

> With this schema, one should be able to do:
> 
> Gender.new(name="Male") # Done once
> Gender.new(name="Female") # Done once
> man = Person(name="Geoff", gender="Male")  # somewhere else in the code

This, though... I dunno.  I'm not sure about it at all.

> If one has to pass in an instance of Gender, then it becomes very clumsy 
> and slow, because you have to instantiate the object you want first. i.e.:
> 
> male = Gender.select(Gender.q.name=="Male")
> man = Person(name="Geoff", gender=male)

Also:
male = Gender.selectBy(name="Male")[0]

alternateID is, of course, what's really intended in this situation.

> A big yuk. Makes table driven applications awful.  ----
> 
> 12. Similarly, if a class/table is just being used for integrity checks, 
> returning an object rather than the referenced column within the row is 
> clumsy. Assuming the schema in (11), one would like:
> 
> target = Person.select(Person.q.name=="Geoff")
> print target.gender
> "Male"
> 
> When an object is returned, one would have to know the name of the 
> column and then dereference it:
> 
> target = Person.select(Person.q.name=="Geoff")
> print target.gender.name
> "Male"

Yeah... but I'm still unsold.  You can always do:

class Person(SQLObject):

    def _get_gender(self):
        return self._SO_get_gender().name
    def _set_gender(self, value):
        self._SO_set_gender(Gender.byName(value))

It's not that bad to use properties and create your own attributes, if
SQLObject's attributes don't suite you.  People should do that more -- I
went out of my way to make it possible (and to document it)!  This is
exactly the sort of thing it's there for.

> 13. Being able to add and drop columns at runtime is great. This removes 
> the need to regenerate the tables in a live database. +++
> 
> 14. Modeling (which seems rather hard to get into) seems to have very 
> expressive ways to get complex joins. I haven't tried it at all, but 
> have browsed the docs. Might be worth stealing some ideas from them. But 
> gee, theyReallyLikeLongMethodNames! Or maybe I'm sensitive to them due 
> to RSI...

I've only looked at Modeling a bit.  But ouch... those functions and
methods are painful to read, not to mention write.  Like:

            from Modeling.FetchSpecification import FetchSpecification
            from Modeling.Qualifier import qualifierWithQualifierFormat
        
            qualifier=qualifierWithQualifierFormat('lastName=="Hugo"')
            fetchSpec=FetchSpecification(entityName='Writer', qualifier=qualifier)
            objects=ec.objectsWithFetchSpecification(fetchSpec)
        
That's a "simple" fetch.  Hurts my head just having that on the page.  Oh my:

        aBook.addObjectToBothSidesOfRelationshipWithKey(anAuthor, 'author')

Okay.  I guess that's enough of that, it's not fair to pick on other
ORMs.  Besides joinSemantic, there's not a whole lot else that's not
just standard infrastructure.  joinSemantic has outer joins, but I don't
know what they really mean to an ORM anyway.  I mean, say you want all
people, and you also want their picture if they have one...

for person in Person.select():
    image = person.image
    # and do stuff, image may be None
    # and don't tell me an outer join is easier to understand... ;)

If you want to do it in one query, well, you can't.  ORMs tend to throw
out many more SQL queries.  Some of them can probably be optimized, you
could do so using the selectResults keyword (for instance), but that
does require careful reading of the source to use... maybe that could be
made easier, but I don't think it can (or should) be made seemless.  I
think a lot of uses outer joins are just fine as multiple queries,
that's something you should optimize based on an empirical need.

  Ian

[SQLObject] Re: Bug, was: one to one relationship via foreign key--bug or dumbness?

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

Ian Bicking wrote:

> Okay, *this* time!  Finally wrote the dumb unit test...

Almost there!  :-) I have one failure:

 >>> from test import *
 >>> cc = ComponentCategory.new(name="CAT")
 >>> cc
<ComponentCategory 3 name='CAT' description=None sequenceNum=None>


Create component with default None for category:

 >>> c = Component.new(name="Mad")
 >>> c
<Component 9 name='Mad' categoryId=None sequenceNum=None>
 >>> c.category



Try assigning instance of ComponentCategory to category attribute:

 >>> c.category = cc
 >>> c.category
<ComponentCategory 3 name='CAT' description=None sequenceNum=None>


Now try creating component while passing in an instance of 
ComponentCategory:


 >>> c = Component.new(name="Mad", category=cc)
 >>> c
<Component 10 name='Mad' categoryId='3' sequenceNum=None>
 >>> c.category
Traceback (most recent call last):
   File "<stdin>", line 1, in ?
   File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 
928, in __repr__
     ' '.join(['%s=%s' % (name, repr(value)) for name, value in 
self._reprItems()]))
TypeError: an integer is required


...Edmund.

[SQLObject] Re: Bug, was: one to one relationship via foreign key--bug or dumbness?

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

On Thu, 2003-05-29 at 22:50, Edmund Lian wrote:
> Ian Bicking wrote:
> 
> > Okay, my fix was just all wrong, I mixed up the argument order.  CVS
> > fixed this time, maybe.
> 
> Sorry, not really. My tests:

Okay, *this* time!  Finally wrote the dumb unit test...

[SQLObject] Sort in DESC order

[J-P L. <sql...@si...>]

Hi all,

Can someone send an example of sorting by DESC order?  Is it possible?  
I get an error when using _defaultOrder = 'somecolname DESC'.

Thanks!

J-P