sqlobject-discuss — All things SQLObject-related

[SQLObject] PyPgSQL support?

[xtian <xt...@to...>]

Hi -

It looks like the PostGreSQL support for SQLObject expects psycopg - is 
there any particular reason for this? I'm doing development on Windows 
(although we'll eventually be deploying on Linux), and I don't think I 
can go through the compilation process required for psycopg. (Unless 
anyone has some compiled binaries for windows/Python2.2 lying around?)

It doesn't look like it would take much to make PostgresConnection use 
PyPgSQL.PgSQL (which has binaries for windows), although I haven't 
looked closely. Are there any nasty gotchas I should know about?  Have 
braver men tried and failed?

Thanks,
Christian

Re: [SQLObject] RelatedJoin bug

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

Luke Opperman wrote:

> Care to elaborate on the design reasons for sticking these both
> in one table instead of two?

Sure... just give me a day or so to get back to you...


...Edmund.

Re: [SQLObject] RelatedJoin bug

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

> Yes, it seems wierd the way I do it, but not really. What I'm trying to
> do (using my Cat/Dog/Person example) is capture the relationship between
> a person and all his/her animals within the same mapping table. There
> are a variety of legitimate reasons for doing so.
>
> When you do this, a null value in one row need not necessarily indicate
> invalid data. I do have a check constraint to ensure that each row has
> at least pair of non-null values.

Hmm, I was just going to reply to your other post that this merged intersection
table seems odd. I have a few three-way intersections in my latest project,
but not where either-this-or-that-or-both is the meaning (no NULLs here). Hmm.

I've proposed that joins silently pass over NULL values before, but always
changed my mind because it always seemed to point to bad data for us, and we
didn't want to hide that. I suppose it could become a constructor argument for
the join, allowNulls(?). But I'm only convinced by the legacy possibility of
your table. :) Care to elaborate on the design reasons for sticking these both
in one table instead of two?

- Luke

Re: [SQLObject] RelatedJoin bug

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

Ian Bicking wrote:

> hmm... well, I made a change that I think should work.  I also got rid of the
 > funny behavior you got when you accidentally instantiated an object 
with an id of None.

Yup, you got it, thank you!

...Edmund.

Re: [SQLObject] RelatedJoin bug

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

Ian Bicking wrote:

> You can put do createTable(createJoinTables=False).  Otherwise it should
> create the join table when you create the table that's alphabetically
> first (so both createTables don't create the shared table).  Is that not
> working for you?

No, I just didn't think of doing this. I will now!

> Why would there by a null value?  Each row is a relationship, so a row
> with a null should just be deleted.  Maybe it's because you are using it
> all funny like, with a triple join... hmm... well, I made a change that
> I think should work.  I also got rid of the funny behavior you got when
> you accidentally instantiated an object with an id of None.

Yes, it seems wierd the way I do it, but not really. What I'm trying to 
do (using my Cat/Dog/Person example) is capture the relationship between 
a person and all his/her animals within the same mapping table. There 
are a variety of legitimate reasons for doing so.

When you do this, a null value in one row need not necessarily indicate 
invalid data. I do have a check constraint to ensure that each row has 
at least pair of non-null values.

...Edmund.

Re: [SQLObject] FK bug? (object has no attribute 'id')

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

Luke Opperman wrote:

> Ran into a bug with (at least) ForeignKeys. Actually, the bug is when we access
> an attribute of an FK object that is a null entry. (Clearly, we shouldn't be
> doing this, but the bug is that it's not raising NotFound, but getting through
> to the attribute).

Looks like the same, or closely related bug as mine.

...Edmund.

Re: [SQLObject] RelatedJoin bug

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

On Tue, 2003-06-03 at 22:07, Edmund Lian wrote:
> I've found a couple of bugs in RelatedJoin...
> 
> 1. If you're going to explicitly define a class for the mapping table, 
> you cannot use .createTable() to create tables on either side of the 
> join. This is because the RelatedJoin declaration forces SQLObject to 
> try to create the mapping (intermediate) table again, and this causes an 
> exception.

You can put do createTable(createJoinTables=False).  Otherwise it should
create the join table when you create the table that's alphabetically
first (so both createTables don't create the shared table).  Is that not
working for you?

> 
> 2. When the mapping table table is used to map two or more many:many 
> relationships, the presence of null values in the mapping table causes 
> an exception. Example test case:

Why would there by a null value?  Each row is a relationship, so a row
with a null should just be deleted.  Maybe it's because you are using it
all funny like, with a triple join... hmm... well, I made a change that
I think should work.  I also got rid of the funny behavior you got when
you accidentally instantiated an object with an id of None.

  Ian

Re: [SQLObject] FK bug? (object has no attribute 'id')

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

On Tue, 2003-06-03 at 23:46, Luke Opperman wrote:
> Ran into a bug with (at least) ForeignKeys. Actually, the bug is when we access
> an attribute of an FK object that is a null entry. (Clearly, we shouldn't be
> doing this, but the bug is that it's not raising NotFound, but getting through
> to the attribute).

Yes, this *did* happen, for reasons to boring to go into, but it
shouldn't anymore (as of a couple days ago).  Is this happening with a
fresh CVS checkout?

  Ian

[SQLObject] FK bug? (object has no attribute 'id')

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

Ran into a bug with (at least) ForeignKeys. Actually, the bug is when we access
an attribute of an FK object that is a null entry. (Clearly, we shouldn't be
doing this, but the bug is that it's not raising NotFound, but getting through
to the attribute).

  ...
    groupName = category.artworkGroup.name
  File "<string>", line 1, in <lambda>
  File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 692, in
_SO_getValue
    results = self._connection._SO_selectOne(self,
[self._SO_columnDict[name].dbName])
  File "/usr/lib/python2.2/site-packages/SQLObject/DBConnection.py", line 254,
in _SO_selectOne
    return self.queryOne("SELECT %s FROM %s WHERE %s = %s" %
AttributeError: 'ArtworkCategory' object has no attribute 'id'

this is when category.artworkGroupID is NULL.

I'll look into this further tomorrow. In my glancing today I couldn't determine
why instantiating the artworkGroup object doesn't blow up, which is the
expected behavior, but instead an object WITHOUT an id field is being created.
Edmund's report may be related to this however, which is why i bring it up...
For now, just starting a thread to talk to myself in...

- Luke

[SQLObject] RelatedJoin bug

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

I've found a couple of bugs in RelatedJoin...

1. If you're going to explicitly define a class for the mapping table, 
you cannot use .createTable() to create tables on either side of the 
join. This is because the RelatedJoin declaration forces SQLObject to 
try to create the mapping (intermediate) table again, and this causes an 
exception.


2. When the mapping table table is used to map two or more many:many 
relationships, the presence of null values in the mapping table causes 
an exception. Example test case:

Schema
------
create table cat (
     id     serial,
     name   text
);

create table dog(
     id     serial,
     name   text
);

create table person(
     id     serial,
     name   text
);

create table owner_map(
     id         serial,
     person_id  integer,
     cat_id     integer,
     dog_id     integer
);


Classes
-------
class Dog(SQLObject):
     _columns = [
         StringCol("name")]
     _joins = [
         RelatedJoin("Person",
             intermediateTable = "owner_map",
             joinColumn = "dog_id",
             otherColumn = "person_id")]

class Cat(SQLObject):
     _columns = [
         StringCol("name")]
     _joins = [
         RelatedJoin("Person",
             intermediateTable = "owner_map",
             joinColumn = "cat_id",
             otherColumn = "person_id")]

class Person(SQLObject):
     _columns = [
         StringCol("name")]
     _joins = [
         RelatedJoin("Dog",
             intermediateTable = "owner_map",
             joinColumn = "person_id",
             otherColumn = "dog_id"),
         RelatedJoin("Cat",
             intermediateTable = "owner_map",
             joinColumn = "person_id",
             otherColumn = "cat_id")]

class OwnerMap(SQLObject):
     _columns = [
         IntCol("personId"),
         IntCol("dogId", foreignKey="Dog"),
         IntCol("catId", foreignKey="Cat")]


How to raise the exception:
 >>> d = Dog.new(name="Snoopy")
 >>> c = Cat.new(name="Garfield")
 >>> p = Person.new(name="John")
 >>> p.addDog(d)
 >>> p.dogs
[<Dog 2 name='Snoopy'>]
 >>> p.cats
[Traceback (most recent call last):
   File "<stdin>", line 1, in ?
   File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 
925, in __repr__
     return '<%s %i %s>' \
AttributeError: 'Cat' object has no attribute 'id'
 >>>

What's happening (I think) is that the query that SQLObject uses to find 
the right row in the mapping table is assuming that either no row is 
found, or that if a row is found, then a not null value for cat_id will 
exist. So the null/None value for cat_id in owner_map causes an exception.

I'd fix it, but I don't understand SQLObject well enough to do so. Sorry...


...Edmund.

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

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

Nick wrote:

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

Here, here. Gerhard has been putting in changes, and it does raise 
IntegrityErrors now too.

But, the real problem lies in the DB-API's silence on more detailed, 
cross-DB error messages. I ran into this problem a few months back when 
I was trying to write some reasonably DB independent code. Using simple 
SQL would generally do the trick, but since there were no standard 
exceptions to give detailed errors (e.g., foreign key constraint 
violation versus duplicate key insertion), I crashed and burned.

...Edmund.

Re: [SQLObject] ADO DB-API support?

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

Since this is all more DBAPI-related than SQLObject-related, and quoting
is popping up (yet again :) on the DB-SIG list, maybe this discussion
should be moved there.

Of course, a lingering prerequesite for this all is a SQL parser to
handle the ?'s, the existence of which makes quoting hooks more
generally interesting (since placeholders aren't currently portable,
making the quoting portable is less interesting).

On Tue, 2003-06-03 at 04:38, Magnus Lyck=E5 wrote:
> At 02:36 2003-06-03 -0500, Ian Bicking wrote:
> >If you want class X to act differently for two different databases, th=
en
> >you either need something more than __sqlrepr__ (like __sqlrepr_pg__,
> >__sqlrepr_oracle__), or maybe __sqlrepr__ gets called with the driver
> >name, or you implement your own thing in the quote function.
>=20
> __sql_type__ or __sql_literal__ would need to be combined with a
> unified way of representing the date in question. Then it's up
> to the db driver that supports __sql_literal__ =3D> DATE to be able
> to convert from a common format to the backend specific.
>=20
> I suggest that we simply catalog literal formats beyond the ones
> described in the SQL standard. Obviously BOOL is needed for instance.
> But remember: We don't need to keep track of every *TYPE*, only every
> kind of *LITERAL*. There are a whole bunch of numeric types for
> instance, but only two types of numeric literals, EXACT (e.g. -0.2)
> and APPROXIMATE (e.g. 31.4159E-1).
>=20
> If as a certain kind of literal is only useful for one particular
> driver/backend, we might as well use RAW, but as soon as more than
> one driver/backend supports a literal, we should have a standard
> way of describing that literal.
>=20
> I'm beginning to feel that maybe we should have a __sql_value__
> rather than __sql_repr__ ... Something like in this example:
>=20
> class Bool(int):
>      def __new__(cls, val):
>          return int.__new__(cls, val and 1 or 0)
>      def __sql_literal__(self):
>          return db.literal.BOOL
>      def __sql_value__(self):
>          return self
>=20
> Then execute for PostgreSQL could do something like...
>      ...
>      elif value.__sql_literal__(self) =3D=3D literal.BOOL:
>          return ['FALSE', 'TRUE'][value.__sql_value__()]
>      ...
>=20
> >For instance, lets say datetime defines its own __sqlrepr__ that outpu=
ts
> >a string with an ISO date as its contents.  But now you, not the autho=
r
> >of datetime, finds out Access doesn't like that, so you override quote
> >and do a special check to fix up this specific case.
>=20
> In a case like that, you can always use RAW. This means that your class
> will have to give different results on calls to __sql_literal__ and
> __sql_repr__ on different backends, but that's doable, isn't it? I
> guess passing in the dbmodule or it's name might make in easier, but
> I think this should be an exceptional thing. For the exceptional
> cases you could build explicit SQL strings.
>=20
> I don't want it to become a norm that the applications contain a lot
> if backend specific conversion code. After all, that's what parameter
> passing should handle for us.
>=20
> What I want to provide is a method to inform the db interface how it
> should handle an object, when this object is not a type or class that
> it knows.
>=20
> A catalog could perhaps start like this:
>=20
> __sql_literal__  __sql_value__
> RAW              string, not to be quoted or escaped.
> CHAR             string
> NCHAR            unicode string
> NUMBER           float, long or int
> BOOL             1 (True) or 0 (False)
> DATE             mx.DateTime.DateTime (or the new datetime)
> TIMESTAMP        mx.DateTime.DateTime
> TIME             mx.DateTime.DateTimeDelta
> INTERVAL         mx.DateTime.DateTimeDelta
> BIT              string containing only [01]
> HEX              string containg only [0-9A-Fa-f]
> NULL             None
> BLOB             binary string or stream object (StringIO, File...)?
>=20
>=20
>=20
> --
> Magnus Lycka (It's really Lyckå), ma...@th...
> Thinkware AB, Sweden, www.thinkware.se
> I code Python ~ The Agile Programming Language=20

Re: [SQLObject] New style classes and __init__ vs _init

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

Hi Bud,

> I think what I know about this issue, I read from either
> in Andrew Kuchlin's What's new in 2.2
> http://www.python.org/doc/2.2.1/whatsnew/
> or in Guido's essay
> http://www.python.org/doc/2.2.1/whatsnew/
> 
> I agree with Ian that every time you call the class, both __new__ and
> __init__ of the class are executed.  

Thanks for the link. I found the discussion I was looking for in: 
http://www.python.org/2.2/descrintro.html which is what I think your 
second link was meant to be.

I wasn't doubting Ian--just that I couldn't find the discussion. 
Kuchling doesn't actually talk about it. I completely missed the 
discussion about this when I first read up on 2.2. Duh.

...Edmund.

Re: [SQLObject] New style classes and __init__ vs _init

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

Hi Edmund,

I think what I know about this issue, I read from either
in Andrew Kuchlin's What's new in 2.2
http://www.python.org/doc/2.2.1/whatsnew/
or in Guido's essay
http://www.python.org/doc/2.2.1/whatsnew/

I agree with Ian that every time you call the class, both __new__ and
__init__ of the class are executed.  

More precisely, if

class C(object):
  def __init__(self):
    pass

c = C() will translate to something along the following lines:
c = C.__new__(C) followed by C.__init__(c) 

But as I pointed out before, calling the class is not the only way of
ending up with a valid instance of C.  It is possible, and often
useful to just call __new__ and to initialize the state of the object
(in most cases the value of __dict__) in a different way.  This is
what is done for example by pickle.  

--b



On Mon, 02 Jun 2003 20:25:35 -0400
Edmund Lian <el...@in...> wrote:

> 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.
> 
> 
> 
> -------------------------------------------------------
> 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] ADO DB-API support?

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

At 02:36 2003-06-03 -0500, Ian Bicking wrote:
>If you want class X to act differently for two different databases, then
>you either need something more than __sqlrepr__ (like __sqlrepr_pg__,
>__sqlrepr_oracle__), or maybe __sqlrepr__ gets called with the driver
>name, or you implement your own thing in the quote function.

__sql_type__ or __sql_literal__ would need to be combined with a
unified way of representing the date in question. Then it's up
to the db driver that supports __sql_literal__ => DATE to be able
to convert from a common format to the backend specific.

I suggest that we simply catalog literal formats beyond the ones
described in the SQL standard. Obviously BOOL is needed for instance.
But remember: We don't need to keep track of every *TYPE*, only every
kind of *LITERAL*. There are a whole bunch of numeric types for
instance, but only two types of numeric literals, EXACT (e.g. -0.2)
and APPROXIMATE (e.g. 31.4159E-1).

If as a certain kind of literal is only useful for one particular
driver/backend, we might as well use RAW, but as soon as more than
one driver/backend supports a literal, we should have a standard
way of describing that literal.

I'm beginning to feel that maybe we should have a __sql_value__
rather than __sql_repr__ ... Something like in this example:

class Bool(int):
     def __new__(cls, val):
         return int.__new__(cls, val and 1 or 0)
     def __sql_literal__(self):
         return db.literal.BOOL
     def __sql_value__(self):
         return self

Then execute for PostgreSQL could do something like...
     ...
     elif value.__sql_literal__(self) == literal.BOOL:
         return ['FALSE', 'TRUE'][value.__sql_value__()]
     ...

>For instance, lets say datetime defines its own __sqlrepr__ that outputs
>a string with an ISO date as its contents.  But now you, not the author
>of datetime, finds out Access doesn't like that, so you override quote
>and do a special check to fix up this specific case.

In a case like that, you can always use RAW. This means that your class
will have to give different results on calls to __sql_literal__ and
__sql_repr__ on different backends, but that's doable, isn't it? I
guess passing in the dbmodule or it's name might make in easier, but
I think this should be an exceptional thing. For the exceptional
cases you could build explicit SQL strings.

I don't want it to become a norm that the applications contain a lot
if backend specific conversion code. After all, that's what parameter
passing should handle for us.

What I want to provide is a method to inform the db interface how it
should handle an object, when this object is not a type or class that
it knows.

A catalog could perhaps start like this:

__sql_literal__  __sql_value__
RAW              string, not to be quoted or escaped.
CHAR             string
NCHAR            unicode string
NUMBER           float, long or int
BOOL             1 (True) or 0 (False)
DATE             mx.DateTime.DateTime (or the new datetime)
TIMESTAMP        mx.DateTime.DateTime
TIME             mx.DateTime.DateTimeDelta
INTERVAL         mx.DateTime.DateTimeDelta
BIT              string containing only [01]
HEX              string containg only [0-9A-Fa-f]
NULL             None
BLOB             binary string or stream object (StringIO, File...)?



--
Magnus Lycka (It's really Lyckå), ma...@th...
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The Agile Programming Language 

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

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

On Sun, 2003-06-01 at 01:46, Edmund Lian wrote:
> 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?

It looks like you got this resolved, and that's probably the right
resolution as well.  After the next release I'm going to beef up the
join stuff a bit more, and it will be possible to add extra values to
the join.  So you might do (in my as-yet-imaginary extension):

class Person(SQLObject):
    dogs = ExtensibleRelatedJoin('Dog', extraColumn='times_per_day')

class Dog(SQLObject):
    people = ExtensibleRelatedJoin('Person', 
                 extraColumn='times_per_day')

d = Dog.new()
p = Person.new()
d.addPerson(p, 1) # p walks d once per day
p2 = Person.new()
d.addPerson(p2, 3) # p2 walks d three times per day (lots of walking)
d.people
>>> [(<Person 1>, 1), (<Person 2>, 3)]
p.dogs
>>> [(<Dog 1>, 1)]


If the relationship starts getting more complicted, than full SQLObject
classes for the relation table are probably called for.

  Ian

Re: [SQLObject] ADO DB-API support?

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

On Thu, 2003-05-29 at 11:27, Magnus Lyck=E5 wrote:
> At 22:31 2003-05-28 -0500, Ian Bicking wrote:
> >I think __sqltype__ seems a little awkward.  You have to agree on the
> >types (and type names) that the backend accepts, and that gets into a
> >whole discussion that seems rather endless ;)
>=20
> Most of the time, you only need to tell the DB-API whether it might
> need to escape/quote the data or not. In other words, is this a number
> or a string? If the class above the DB-API is to handle escape/quote,
> it would need to supply "that's" from __sqlrepr__ when it works with i.=
e.
> mxODBC or cx_Oracle, and "'that''s'" when it works with MySQLdb or a
> PostgreSQL driver. If it can just tell the DB-API whether it's a string
> or a number, it can always supply "that's", or "5" or "314159E-5", and
> the driver can handle it just like it does for ints, floats and strings
> etc today. This covers the overwhelming majority of used literals.
>=20
> Actually, it's not really the data type in the database that matters,
> but what kind of *literal* we are providing. Maybe the method should
> be called __sqlliteral__ or __sql_literal__ rather than __sqltype__
> (and the other method __sql_repr__ if we use __sql_literal__)?
>=20
> The SQL/92 standard supports four kinds of literals:
>=20
> character string    ('that''s')
> bit string          (B'01010101' or X'f5a1')
> exact numeric       (4 or +365.2425)
> approximate numeric (4E3 or -.15E-15)

The problem is that those are the literals that don't matter -- since
SQL92 defines them, most all SQL databases handle them just fine.  It's
other literals -- like dates -- that are likely to have problems. =20

> In addition to returning any of these, I think __sql_literal__ should
> be able to return 'raw' to inticate that the driver should just pass
> the value in as it is. It's then up to the application programmer to
> provide the right data. If she has to support many tricky backends and
> don't have very high performance requirements, she could put logic into
> the __sql_repr__ method that will check what backend it is and act base=
d
> on that.
>=20
> Obviously, MS Access is in clear violation with the SQL spec here, as i=
n
> a number of other cases :( so *this* would not solve the #1900-01-01#
> problem.
>=20
> I'd like to suggest the following: Each db driver has to supply a
> CONSTANT for each kind of literal it supports. At least, it should
> support the following constants:
> .literal.CHAR            Action for such values is typically
>                           "'%s'" % x.replace("'", "''")
> .literal.BBIT (or BITS?) Action is typically "B'%s'" % x
> .literal.XBIT (or HEX?)  Action is typically "X'%s'" % x
> .literal.EXACT           Action is typically to use as is
> .literal.APPROX          Action is typically to use as is
> .literal.RAW             Action is always to us as is.
>=20
> It could optionally support other literal types such as
> .literal.DATE  which could provide custom handling of date
>                 strings for some peculiar backend,
> .literal.MONEY if this is ever needed...
> .literal.BIN   to let people pass in binary strings and not need to
>                 convert them to B'0101' or X'ffff'. This could then
>                 pass the binary data as it is if the backend supports
>                 that, or do "X'%s'" % x.encode('hex') as a convenience
>                 for the application programmer. I don't know if there i=
s
>                 a desire to be able to provide a stream instead of a st=
ring
>                 for binary data? How do people handle BLOBs today? But
>                 maybe I'm confused here. Backends might not handle bit
>                 fields and BLOBs etc in the same way.

It's only these non-standard ones that seem useful (besides RAW).  Which
is why this seems problematic.

[snip]
> Something like this...
>=20
> # mydate.py
>=20
> def init_module(dbdriver):
>      global sql_literal
>      try: sql_literal =3D dbdriver.literal.DATE
>      except: sql_literal =3D dbdriver.literal.CHAR

That init_module is a killer -- it's the same problem you get with
database exceptions right now, where you can't be database agnostic in
isolated parts of code.  The optional stuff will be a pain too -- maybe
if there's a common DBI module it'd be more workable.

[snip quote function example]
> What if dbdriver is written in c? (I guess you can always have
> a thin wrapper.)

Mmm... no assigning the function in that case, I suppose.  Well, you
could instead have all the drivers use a setQuoter() function, which
could just reassign quote(), or do something else if that's not
possible.  The concept is really just about overriding a quoting
function in some manner.

> >The quote function is potentially database specific.  After thinking
> >about it, this function should have the chance to get at the raw value
> >before __sqlrepr__ is called.
>=20
> I don't see why. If you want class X to behave differently in some
> situation, subclass it and put the different behaviour in __sqlrepr__
> of the subclass. These functions seem redundant to me. One is more
> OO, the other more procedural.

If you want class X to act differently for two different databases, then
you either need something more than __sqlrepr__ (like __sqlrepr_pg__,
__sqlrepr_oracle__), or maybe __sqlrepr__ gets called with the driver
name, or you implement your own thing in the quote function.

For instance, lets say datetime defines its own __sqlrepr__ that outputs
a string with an ISO date as its contents.  But now you, not the author
of datetime, finds out Access doesn't like that, so you override quote
and do a special check to fix up this specific case.

If you want __sqlrepr_oracle__, you could also implement that in the
quote function.  It's a general hook, and one which is tied to the
database, not the objects being quoted.

> >Anyway, people talked serious about including Gadfly in the standard
> >distribution, so there's interest in supporting database stuff.  The
> >only weird part about the module is that it would be useless on its ow=
n.
>=20
> If Gadfly was included and used this module, it would be better. I
> would rather see sqlite in the standard library though. I don't see
> why we can have bsddb and not sqlite. SQLite is much more like a typica=
l
> SQL database than Gadfly is.

Yes, I like SQLite more as well, but it's not so much Gadfly in
particular -- rather, there are people interested in putting database
stuff into the main Python distribution.  So it doesn't seem impossible
that a common DBI module could also be included.

  Ian

Re: [SQLObject] Re: Impressions so far

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

On Sat, 2003-05-31 at 23:36, Edmund Lian wrote:
> > 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.

Yes, I'd want to be able to do conversion based on the backend.  Like
ENUM is simulated, BOOL should be simulated too.

I guess I'll have to keep in mind that the validation/conversion has to
be potentially database-specific.

  Ian

Re: [SQLObject] PickleCol?

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

On Fri, 2003-05-30 at 03:56, Bud P.Bruegger wrote:
> So what do you think of a PickleCol?

I'd implement it as one of the mythical validator/converters (well, at
least the use in SQLObject is mythical).

Though it'd probably be useful to then do a PickleCol, that adds that
validator and has a SQL type of TEXT or BLOB or something.

  Ian

Re: [SQLObject] Sort in DESC order

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

On Fri, 2003-05-30 at 12:59, J-P Lee wrote:
> Can someone send an example of sorting by DESC order?  Is it possible?  
> I get an error when using _defaultOrder = 'somecolname DESC'.

I'm afraid I haven't implemented this yet -- a little detail that's
slipped by.  It'll get into the next release, which should happen soon.

  Ian

Re: [SQLObject] Namespace pollution

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

On Sun, 2003-06-01 at 02:35, Edmund Lian wrote:
> 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.

Hmm... well, there are some private variables that hold information,
like _SO_columnDict, in addition to the public _columns and _joins (poor
naming aside).  There's nothing very public at this point.  Like with
most of these things, I don't want to commit to anything until I know
how it's *really* going to be used -- mostly to design the interface
more appropriately.

  Ian

Re: [SQLObject] Re: Impressions so far

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

On Mon, 2003-06-02 at 03:55, Edmund Lian wrote:
> 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!

Thanks, I'm glad you both like SQLObject.

> 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.

I don't mind comments at all -- thinking back on them, I don't actually
take a whole lot of the suggestions that are given, but in the end I at
least have a better idea of why I'm doing one thing and not the other,
even if at the root it's mostly based on intuition.  Either way it just
brings me one step closer to WORLD DOMINATION!

  Ian

Re: [SQLObject] Re: Impressions so far

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

On Sat, 2003-05-31 at 21:47, Luke Opperman wrote:
> > 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...)

Yeah, maybe the two are rather redundant.  I wouldn't worry about the
method creation overhead, but more about having useless methods laying
around.

Maybe alternateID should be removed, and unique would imply
alternateID.  alternateMethodName would become fetchMethodName, and it
would be required if you wanted what is now byColName.

> > 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?

Exactly -- all the fancy SELECT statements create results that aren't a
homogeneous list of database objects.  *Some* statements could be turned
into a list of tuples or dictionaries of database objects, like [(p,
p.car) for p in Person.select()] -- and maybe being able to product that
same sort of result from a single SQL query would be useful in SQLObject
(though a lot of work to implement, and usually not necessary)...

> > 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.

Ah... SiteSQLObject ;)  SitePage annoys me sometimes, though... should
there be a better way to do this?

> > 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 ...?)

I want to rename all the *Connection classes to be *Store, after this
next release.  Then _connection becomes _store, or maybe __store__.  Is
that the renaming you are thinking about?

> > 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)..

SQLBuilder could handle it with just a simple (but not yet existent)
function, but most people aren't using SQLBuilder for orderBy (like
Person.select(orderBy=(Person.q.lname, Person.q.fname)).

I'll add an option to SelectResults, probably "descending", that will do
this.

  Ian

Re: [SQLObject] Re: Impressions so far

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

On Fri, 2003-05-30 at 21:48, Ian Bicking wrote:
> 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')

After a little thought, and in Modeling's defense, I have a feeling this
is because the API was taken from an Object C ORM, where these method
names don't look quite so odd.  More like:

aBook addObjectToBothSidesOfRelationship: anAuthor withKey: "author"

OK... "addObjectToBothSidesOfRelationship" is still a little long, but
this sort of method name is more common in that world.  It still could
stand to be translated into better Python -- "withX" shouldn't be used,
for instance, instead it should be key='author' -- but at least it's
more understandable.

  Ian

[SQLObject] Re: Impressions so far

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

On Sat, 2003-05-31 at 14:40, Edmund Lian wrote:
> 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.

That would be fine.  If you can enumerate all those, and suggest how
they'd be specified in Python, I'm happy to add them. 

> >>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.

Yes, like Luke said, it is a problem with 't' and 'f'.  You'd have to
do:

    someBool = Col(sqlType='BOOLEAN')

    def _set_someBool(self, value):
        if value:
            self._SO_set_someBool(self, 't')
        else:
            self._SO_set_someBool(self, 'f')

I should remember to put this recipe in the docs, since a fix won't come
until the version after the upcoming version.

> 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?

Well, you would do a loop, but at least it's a pretty loop, thanks to
list comprehension:

[p.username, (p.car and p.car.make) for p in Person.select()]

You do more SQL queries in this version, but in most cases I doubt that
will be a performance problem.

> > notNull=True or notNone=True as a keyword argument to your column.
> 
> OK, it wasn't in the manual, or I'm going blind.

Mmm... yeah, I don't think I've documented any of the schema generation
yet.  I've been redoing the documentation just in the last week or so,
updating for the next release.

> 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.

Mostly I'd use a single module where you define the connection,
exporting a single "connection" variable, and then all the other classes
would use that.

I'd thought about naming connections before, so you'd say:

    _connection = "main"

And somewhere else:

PostgresConnection(name="main", ...)

And then it'd be registered.  Maybe that'd be useful to put back in.

> 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?

Luke gave a good solution to this.  It should be generalized, though.

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

Nowhere now.  I should add an option to SelectResults and a function to
SQLBuilder... I'll do that before I make the next release, since it
should just be a small change.

  Ian