sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] Re: SQL Object's scalability

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

On Wed, 2003-11-12 at 14:19, Ian Bicking wrote:
> The current scheme is convenient, but I don't know if it's really worth 
> it.  Should we go back to preallocating an ID through a 
> nextval(sequence)?

psycopg, at least, supports lastrowid for cursor objects now, which may
help.

Nick

[SQLObject] Re: SQL Object's scalability

[Scott C. <sco...@mi...>]

On Wednesday 12 November 2003 12:19, Ian Bicking wrote:
> On Nov 12, 2003, at 1:04 PM, Scott Chapman wrote:
> > Hi Ian,
> > A while back I asked how you get the inserted ID of a record in
> > PostgreSQL and Luke sent back:
> >
> > Depends on the database/dbdriver. You'll find the implementation in
> > SQlObject/DBConnection.py, _queryInsertID() for each
> > DBConnection-class. Some Python db interfaces provide a
> > non-standard option for getting this from the cursor (MySQL,
> > SQLite), some require a second query of the database (Postgres
> > using OIDs, for instance).
> >
> > I recently saw a post on the Postgres mailing list on this so I put
> > up your code as I was asking for clarification.  You might want to
> > check out the thread:
> >
> > http://archives.postgresql.org/pgsql-general/2003-11/msg00376.php
> >
> > especially the message:
> >
> > http://archives.postgresql.org/pgsql-general/2003-11/msg00565.php
>
> Hmm... Does anyone else have thoughts on this?  Basically it's an
> issue that the query "SELECT %s FROM %s WHERE oid = %s" % (idName,
> table, c.lastoid()) may not be using an index on the oid= portion,
> which would be problematic.

It's also an issue where not all tables have OID's these days. 

I posted a followup question on the PostgreSQL mailing list about the 
need for the front end (SQLObject in this case) to know "too much" 
about the database's structure and asked if there's an easy way to fix 
this.  So far no good answers appear.

> At one time SQLObject used nextval to pre-allocate the ID, then did
> the insert (which is what Firebird does now, and what Oracle and
> Sybase would do).  The disadvantage of this is that you needed to
> know the name of the sequence, which would sometimes vary (and maybe
> there were truncation problems).  The disadvantage with the current
> scheme (besides the index) is that you need to define your ID field
> as "DEFAULT nextval('somesequence')" (or do that implicitly through
> SERIAL).

What do you mean "truncation problems"?

> The current scheme is convenient, but I don't know if it's really
> worth it.  Should we go back to preallocating an ID through a
> nextval(sequence)?

I see this as the only way to do it effectively as things are now.  This 
is not a very elegant solution but that's the way it goes.

The convenience could be made the same for an end-user of the SQLObject, 
perhaps.  You could also make it so the user had more than one option 
here. (I am very fond of user-togglable in cases like this).

> Really, to facilitate non-integer IDs, it would be nice if we could
> define a generic ID generator (which may or may not access the
> database).  But I'm not really clear about all the use cases for
> that, so it's probably not a good idea to generalize that yet.

A generic ID generator in the front end (not the database itself)?  That 
sounds like a Bad Thing, in that you'd better be SURE that no other 
application inserts anything into the database because it won't have 
access to your generator to generate a unique ID.

[SQLObject] Re: SQL Object's scalability

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

On Nov 12, 2003, at 1:04 PM, Scott Chapman wrote:
> Hi Ian,
> A while back I asked how you get the inserted ID of a record in 
> PostgreSQL and Luke sent back:
>
> Depends on the database/dbdriver. You'll find the implementation in 
> SQlObject/DBConnection.py, _queryInsertID() for each 
> DBConnection-class. Some Python db interfaces provide a non-standard 
> option for getting this from the cursor (MySQL, SQLite), some require 
> a second query of the database (Postgres using OIDs, for instance).
>
> I recently saw a post on the Postgres mailing list on this so I put up 
> your code as I was asking for clarification.  You might want to check 
> out the thread:
>
> http://archives.postgresql.org/pgsql-general/2003-11/msg00376.php
>
> especially the message:
>
> http://archives.postgresql.org/pgsql-general/2003-11/msg00565.php

Hmm... Does anyone else have thoughts on this?  Basically it's an issue 
that the query "SELECT %s FROM %s WHERE oid = %s" % (idName, table, 
c.lastoid()) may not be using an index on the oid= portion, which would 
be problematic.

At one time SQLObject used nextval to pre-allocate the ID, then did the 
insert (which is what Firebird does now, and what Oracle and Sybase 
would do).  The disadvantage of this is that you needed to know the 
name of the sequence, which would sometimes vary (and maybe there were 
truncation problems).  The disadvantage with the current scheme 
(besides the index) is that you need to define your ID field as 
"DEFAULT nextval('somesequence')" (or do that implicitly through 
SERIAL).

The current scheme is convenient, but I don't know if it's really worth 
it.  Should we go back to preallocating an ID through a 
nextval(sequence)?

Really, to facilitate non-integer IDs, it would be nice if we could 
define a generic ID generator (which may or may not access the 
database).  But I'm not really clear about all the use cases for that, 
so it's probably not a good idea to generalize that yet.

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

[SQLObject] ANN: SQLObject 0.5.1

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

SQLObject 0.5.1 is up.  This includes mostly minor bugfixes.

 From News.txt:

Interface Changes
-----------------

* Select results no longer have a __len__ method (i.e., you can't do
   ``len(Person.select(Person.q.firstName=='Bob'))``).  There is now a
   ``.count()`` method instead.  ``__len__`` gets called implicitly in
   several circumstances, like ``list()``, which causes potentially
   expensive queries to ``COUNT(*)``.

Bugs
----

* Objects retrieved from a join now respect the transaction context of
   the original instance.
* ``.select().reversed()`` works.

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

Re: [SQLObject] Reversed ordering, again

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

On Nov 11, 2003, at 12:11 AM, Randall Randall wrote:
> For Googlers:
>
> In SQLObject 0.5, using psycopg, you can't reverse the
> orderBy of the column 'id'.  You can reverse any column
> you explicitly specified by the name you gave for the
> column.  So this:
>
> MyClass.select().reversed()
>
> doesn't actually work, but you can work around it by
> including another autoincrement column, or a DateTime
> column that uses the SQLBuilder.func.NOW() function
> as its default.  If you're like me, you use that in
> most objects anyway, just in case you might want to
> track creation times later. :)
>
> In the documentation for 0.5, this paragraph:
>
> """
> You can use the keyword arguments orderBy and groupBy to
> create ORDER BY and GROUP BY in the select statements:
> orderBy takes a string, which should be the database
> name of the column, or a column in the form Person.q.firstName;
> groupBy is similar. Both accept lists or tuples of arguments.
> You can use "-colname" to specify descending order, or call
> MyClass.select().reversed().
> """
>
> is wrong as written, and should be
>
> """
> You can use the keyword arguments orderBy and groupBy to
> create ORDER BY and GROUP BY in the select statements:
> orderBy takes a string, which should be the attribute
> name, not the database column name.  Unlike in the select()
> method, you cannot use columns in the form Person.q.firstName
> here; [...] You can use "-attributeName" to specify
> descending order; MyClass.select().reversed() is not
> implemented (or broken) and returns None.
> """
>
> I didn't test groupBy, so omitted that part of this
> paragraph.
>
> It may be that these are considered bugs, or simply weren't
> yet implemented as documented; I don't know.  The lack of
> response the other day suggests the latter. :)

I think it was one of those 
I-meant-to-look-at-it-more-closely-when-I-had-some-time-then-I-forgot 
situations.  There was just a missing "return" in reversed().

It should be fixed now in CVS.

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

Re: [SQLObject] Pool count

[Frank B. <fb...@fo...>]

Hallo,

Ian Bicking hat gesagt: // Ian Bicking wrote:
> Could it just be that you are slowly creeping up the number of 
> concurrent connections?  E.g., you've made 15 concurrent connections, 
> 12 of them have been released, and the increase in numbers are the 
> other 3 slowly returning?  It certainly seems possible that connections 
> could get completely lost somewhere, or only slowly return as iterators 
> are garbage collected.  But that shouldn't cause the actual pool to 
> grow unduly.
> 
> What happens if you also add a print to makeConnection?

I will try that when I'm at the dev machine again, but from my short
first observations the count went up constantly. I stopped testing
when it was around 50 or so. 

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] Pool count

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

On Nov 11, 2003, at 4:42 AM, Frank Barknecht wrote:
> I added something like:
>
>             print "self._pool: %s " % len(self._pool)
>
> to DBConnection's getConnection and releaseConnection methods. This
> count seems to only go up constantly:
> ...
> self._pool in get: 12
> self._pool in release: 13
> self._pool in get: 12
> self._pool in release: 13
> self._pool in release: 14
> ...
>
> Is this expected behaviour?

Huh, that's odd.  makeConnection is definitely only supposed to be run 
if the pool is empty.  And the code sure looks like that's what it 
does.  I just don't know why that would possibly happen...

Could it just be that you are slowly creeping up the number of 
concurrent connections?  E.g., you've made 15 concurrent connections, 
12 of them have been released, and the increase in numbers are the 
other 3 slowly returning?  It certainly seems possible that connections 
could get completely lost somewhere, or only slowly return as iterators 
are garbage collected.  But that shouldn't cause the actual pool to 
grow unduly.

What happens if you also add a print to makeConnection?

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

Re: [SQLObject] Join tables

[John B. <jb...@te...>]

On Tuesday 11 November 2003 17:41, Ian Bicking wrote:
> On Nov 11, 2003, at 11:20 AM, John Baker wrote:
> > Ok :-)
> >
> > So please provide me with some code that will do my
> > Ward/Constituency/Area
> > and 'something has many Areas' example... :)
>
> def oneOrNone(val):
>      try:
>          return val[0]
>      except IndexError:
>          return None
>
> class Area(SQLObject):
>      # Really should be a one-to-one join for these...
>      def _get_ward(self):
>          return oneOrNone(Ward.selectBy(area=self))
>      def _get_consituency(self):
>          return oneOrNone(Constiuency.selectBy(area=self))
>      ...
>
> class Ward(SQLObject):
>      area = ForeignKey('Area')
>
> ...
>
>
> Ta-da!  More work if you want to fold the attributes of the connected
> tables into the original Area object.  I'm sure you won't be satisfied
> by this, but it's only bad if you come into the problem with
> inheritance fixed in your mind.  I tend to come at the problem thinking
> in terms of related objects, which works fine right now, and with which
> you can handle the same problems, even if in a different way.

Well attributes in area would be nice, but it'll do for now :)


-- 
John Baker, Senior Developer, TEAM.
http://www.teamenergy.com
Views expressed in this mail are my own.

Re: [SQLObject] Join tables

[John B. <jb...@te...>]

On Tuesday 11 November 2003 17:30, Luke Opperman wrote:
> But as in all opensource projects, who's going to code these things?
> Someone with the itch.

I might have a shot, one day. It depends if I need to use Python on a much 
bigger project that means I have to try to model serious relationships ;-)

> To bring it back to the specific superset issue, I see the following
> points:
>
> a. SQLObject currently stores no information about class relationships
> (inheritance trees) except for explicit joins/fkeys.

Well it doesn't really have to. If we know B and C extend A, we can SELECT 
across tables B and C to find matching rows. Not efficient, but that's 
accepted with the type of mapping.

You (the developer) can also decide to store information. For example, in the 
Java systems I write, I have a ClassType table mapping a fully qualified 
classname to a primary key. I can then have a foreign key from a top level 
object to the ClassType table, for example:

class Thing (SQLObject):
	areas = MultipleJoin("Area")
	classType = ForeignKey("ClassType")

Therefore I can do:

areas.get(classType.get())

(or whatever syntax you'd prefer)

to get the areas inside the Collection. Assuming they are all of the same 
type.

> 1. A syntactic method for describing inheritance. Using python class
> inheritance is the obvious choice. However, that may be overloaded from
> what we're willing to implement (multiple inheritance?). Alternatively, an
> extends/derives method, although it feels less obviously pythonic.

I'd suggest multiple inheritence is a step to far, as that really does get 
very complex. I've never been sold on multiple inheritence, however people 
will want to use it (I'm sure), so adding support would be nice. Take:

class A (SQLObject):

class B (SQLObject):

class C (A, B):

We'd need to foreign keys in C (fk_a and fk_b) to support this. But this can 
be done automatically, I guess.

> Along with this comes the syntax and logic necessary to have joins of the
> Person--*Area type alternatively return subset objects (Homes/Offices), and
> reverse. (actually, reverse might already work... if you define Area to
> have a fkey to Person, both Home and Office will as well. yes.)

Something to keep in mind is we'd want to use table aliasing. Objectmatter 
'forgot' this, and as a result they've well and truely shot themselves in the 
foot as you can't do complex joins.

(Deadline to meet, /me goes back to work)


John

-- 
John Baker, Senior Developer, TEAM.
http://www.teamenergy.com
Views expressed in this mail are my own.

Re: [SQLObject] Join tables

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

On Nov 11, 2003, at 11:20 AM, John Baker wrote:
> Ok :-)
>
> So please provide me with some code that will do my 
> Ward/Constituency/Area
> and 'something has many Areas' example... :)

def oneOrNone(val):
     try:
         return val[0]
     except IndexError:
         return None

class Area(SQLObject):
     # Really should be a one-to-one join for these...
     def _get_ward(self):
         return oneOrNone(Ward.selectBy(area=self))
     def _get_consituency(self):
         return oneOrNone(Constiuency.selectBy(area=self))
     ...

class Ward(SQLObject):
     area = ForeignKey('Area')

...


Ta-da!  More work if you want to fold the attributes of the connected 
tables into the original Area object.  I'm sure you won't be satisfied 
by this, but it's only bad if you come into the problem with 
inheritance fixed in your mind.  I tend to come at the problem thinking 
in terms of related objects, which works fine right now, and with which 
you can handle the same problems, even if in a different way.

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

Re: [SQLObject] Join tables

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

> It can do both!
>
> But you (the people who own the project) need to decide which side of the
> wall
> to sit on. If you're going to go the superset way, then it needs to be
> thought of now rather than later.

Just to clarify my email, yes, I agree! Do both, I think it has always been the
intent of SQLObject to go both ways (although in my mind, not arbitrary object
storage, and never at the expense of python style or relational modelling :).
And yes, I've started looking at the objectmatter site to see what might be
missing.

But as in all opensource projects, who's going to code these things? Someone
with the itch.

To bring it back to the specific superset issue, I see the following points:

a. SQLObject currently stores no information about class relationships
(inheritance trees) except for explicit joins/fkeys.

b. We currently only directly support what the objectmatter docs describe as a
horizontal mapping (as I said, "one complete table per concrete class").

So in order to add the functionality you requested for supersets, two separate
pieces:

1. A syntactic method for describing inheritance. Using python class
inheritance is the obvious choice. However, that may be overloaded from what
we're willing to implement (multiple inheritance?). Alternatively, an
extends/derives method, although it feels less obviously pythonic.

Along with this comes the syntax and logic necessary to have joins of the
Person--*Area type alternatively return subset objects (Homes/Offices), and
reverse. (actually, reverse might already work... if you define Area to have a
fkey to Person, both Home and Office will as well. yes.)

In my mind, this is the OO side of the development (ie, SQLObject would now
have conceptual support)

2. Independently, support for vertical mapping. That is, #1 would work with our
current method of supporting 'inheritance', this support would allow that
syntax and logic to work across specially designated foreignkeys (the
Home-->Area key).

This is the DB/implementation side of the development (extending the conceptual
support to a new db structure).

Comments?

- Luke

Re: [SQLObject] Join tables

[John B. <jb...@dr...>]

Ok :-)

So please provide me with some code that will do my Ward/Constituency/Area
and 'something has many Areas' example... :)


J

On Tue, Nov 11, 2003 at 11:14:40AM -0600, Ian Bicking wrote:
> On Nov 11, 2003, at 4:20 AM, John Baker wrote:
> > If SQLObject is to be aimed at a very limited set of solutions, that 
> > don't
> > lend themselves towards proper OO programming, then perhaps clearly
> > stating this on the website would be a step forward. But at this point 
> > in
> > time, without superset mapping, you cannot model anything more than
> > trivial relationships. This makes it rather unhelpful, which is a shame
> > because it's actually quite easy to use.
> 
> You have to understand, it's easy to use because the mapping is simple 
> and clear.  I am trying to avoid frameworkification in SQLObject, which 
> is a difficult task.  To me, exposed object hierarchies are one of the 
> creepers which can overwhelm a project if left unchecked.
> 
> Now, I'm not sure if that's the case with this example, since the 
> object hierarchies we're talking about are in user code, not the 
> library code.  Still, I'm wary of all things inheritance-related.  Call 
> it the Zope syndrome.
> 
> My intention with SQLObject is not that it be all things to all people, 
> but rather that you can build all things on it.  The more complex 
> SQLObject is, the more disconnect there will be when the complexity of 
> your problem doesn't match the complexity of SQLObject.  As it is 
> SQLObject is clearly an easier foundation for building superset objects 
> than the DBAPI.  To do so it may be a little ad hoc right now, but you 
> have to build ad hoc designs before generalizing anyway, and a little 
> ad hoc design never killed anyone.
> 
> Maybe having done that you'll desperately want to generalize this 
> particular recipe.  I can only say that I would be much happier seeing 
> it phrased in an explicit manner using composition of objects, rather 
> than inheritance, perhaps because I don't treasure inheritance the way 
> a "real OO programmer" might (which is true), or perhaps because I 
> dislike false cognates and subtle disconnects.
> 
> > To the developer, how the data is stored is totally irrelevant. But 
> > when
> > your developer can't actually model relationships because the system of
> > persistence is restrictive, then we have a problem. I've already 
> > provided
> > two or three examples that clearly demonstrates that SQLObject can't do
> > what I want it to do, and I'm hardly trying :-)
> 
> FWIW, you don't have to code those examples as inheritance, and I 
> naturally wouldn't do so.  Composition is quite powerful, even if 
> mundane, and is pleasantly explicit.
> 
> > And as we all, as good OO developers know, if you build on poor
> > foundations, you regret it later in life.
> 
> As all good agile developers know, you make it up as you go along ;)
> 
> --
> Ian Bicking | ia...@co... | http://blog.ianbicking.org
> 

Re: [SQLObject] Join tables

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

On Nov 11, 2003, at 4:20 AM, John Baker wrote:
> If SQLObject is to be aimed at a very limited set of solutions, that 
> don't
> lend themselves towards proper OO programming, then perhaps clearly
> stating this on the website would be a step forward. But at this point 
> in
> time, without superset mapping, you cannot model anything more than
> trivial relationships. This makes it rather unhelpful, which is a shame
> because it's actually quite easy to use.

You have to understand, it's easy to use because the mapping is simple 
and clear.  I am trying to avoid frameworkification in SQLObject, which 
is a difficult task.  To me, exposed object hierarchies are one of the 
creepers which can overwhelm a project if left unchecked.

Now, I'm not sure if that's the case with this example, since the 
object hierarchies we're talking about are in user code, not the 
library code.  Still, I'm wary of all things inheritance-related.  Call 
it the Zope syndrome.

My intention with SQLObject is not that it be all things to all people, 
but rather that you can build all things on it.  The more complex 
SQLObject is, the more disconnect there will be when the complexity of 
your problem doesn't match the complexity of SQLObject.  As it is 
SQLObject is clearly an easier foundation for building superset objects 
than the DBAPI.  To do so it may be a little ad hoc right now, but you 
have to build ad hoc designs before generalizing anyway, and a little 
ad hoc design never killed anyone.

Maybe having done that you'll desperately want to generalize this 
particular recipe.  I can only say that I would be much happier seeing 
it phrased in an explicit manner using composition of objects, rather 
than inheritance, perhaps because I don't treasure inheritance the way 
a "real OO programmer" might (which is true), or perhaps because I 
dislike false cognates and subtle disconnects.

> To the developer, how the data is stored is totally irrelevant. But 
> when
> your developer can't actually model relationships because the system of
> persistence is restrictive, then we have a problem. I've already 
> provided
> two or three examples that clearly demonstrates that SQLObject can't do
> what I want it to do, and I'm hardly trying :-)

FWIW, you don't have to code those examples as inheritance, and I 
naturally wouldn't do so.  Composition is quite powerful, even if 
mundane, and is pleasantly explicit.

> And as we all, as good OO developers know, if you build on poor
> foundations, you regret it later in life.

As all good agile developers know, you make it up as you go along ;)

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

Re: [SQLObject] Join tables

[John B. <jb...@te...>]

On Tuesday 11 November 2003 16:49, Luke Opperman wrote:

> I don't really need to fuel this argument, I happen to be in the

"Discussion".

> "relational calculus is able to represent all my data" camp (Yes, CJ Date
> writes my nighttime prayers :) But there's no reason why we need to agree
> on this, from what's been discussed so far as functionality for SQLObject,
> go for it.

If only I was that 3l13t3 at Python ;-)

> Just need to realize, those of us who are using SQLObject as a "mapping
> from rtables to objects, not objects to tables" aren't going to be on the

It can do both! 

But you (the people who own the project) need to decide which side of the wall 
to sit on. If you're going to go the superset way, then it needs to be 
thought of now rather than later.

Perhaps looking over www.objectmatter.com would benefit a few. That does both 
mapping from tables to Java objects, and vice versa.


John

-- 
John Baker, Senior Developer, TEAM.
http://www.teamenergy.com
Views expressed in this mail are my own.

Re: [SQLObject] Join tables

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

>
> Superset mapping used, I hardly notice the time taken to retrieve an
> object. That's why we use SQL and databases, and with the world virtually
> stuck on OO, that's the future.
>
> At the end of the day, if you think we shouldn't use a database to store
> OO, then perhaps we should all write in PL/SQL ;-)

I don't really need to fuel this argument, I happen to be in the "relational
calculus is able to represent all my data" camp (Yes, CJ Date writes my
nighttime prayers :) But there's no reason why we need to agree on this, from
what's been discussed so far as functionality for SQLObject, go for it.

Just need to realize, those of us who are using SQLObject as a "mapping from
rtables to objects, not objects to tables" aren't going to be on the front
edge of adding these changes. Although there are still lots of places where
we'll have the same desires (more featureful existing Joins for instance) and
more eyes on the code etc.

- Luke

p.s. I'm not trying to say your modelling is "not relational". If you
understand Date's problems with people tossing around "it's OO, no need for
relational data anymore" then you understand me too. But you seem to be more
grounded than most OO proponents, so it's probably just a visceral reaction on
my part. But it's not really an argument for this list, nor an argument that
tends to convert either side. :)

Re: [SQLObject] Join tables

[John B. <jb...@dr...>]

On Tue, Nov 11, 2003 at 10:36:59AM +0000, Andy Todd wrote:
> > DBAs are a dieing breed. After all, who needs a DBA for MySQL or
> > Postgres? Alright, so you may be using Oracle, and I pity you :)
> 
> Well I still get called one occasionally, but in reality I'm what Scott 
> Ambler calls a 'generalising specialist' 

Quite. Someone who sits in a room importing/exporting DBAs doesn't really
provide much useful input into modern life. Code monkeys, which is how I

> > lend themselves towards proper OO programming, then perhaps clearly
> > stating this on the website would be a step forward. But at this point in
> > time, without superset mapping, you cannot model anything more than
> > trivial relationships. This makes it rather unhelpful, which is a shame
> > because it's actually quite easy to use.
> 
> Very limited? Virtually every system I come across these days, both 
> professionally and in my own time, has the application code written in 
> an OO language and the data stored in a relational database. I don't 

Then demonstrate how to map my simple example of
House/Office/Area/Person, as posted in a previous mail.

> Sybase or MySQL) licenses lying about and says "Oh, I know, lets use 
> this for storing our objects". Its because the relational database 
> supplies benefits over and above storing your objects in flat files. 
> Otherwise everyone would be using shelve, right?

They'd use something which supported a mapping type that allows them to do
what they want to do. Only a real idiot would use flat files (I know one,
he's sitting behind me ... :-) over a DB, and the DB can support the type
of structure we require.

But SQLObject can't.
 
> And I come to the situation from the other side, if you are *just* using 
> a databsae to store data then why not just get a decent journalled file 
> system on a RAID disk system and use flat files. That way you aren't 
> constrained by Mr Codd's nasty rules.

Too slow. Writing your own optimised system of storing data to flat files
is a complete waste of your time. A DB will do it perfectly well, this
isn't in dispute. But SQLObject is restrictive and won't let us.

> Correct. But the developer isn't the only actor in this drama. What 
> about extracting data from an operational system to a data warehouse? Or 
> writing summary reports on working data sets? Or replication? Or 
> point-in-time recovery? Or, shock horror - non OO access to your data?

Extracting:
 Dump the tables. You need to do no more than back up the data.
Summary reports:
 You use the system, and objects, you have defined, along with SQLObject,
to write your reports. Writing them any other way would be silly.
Replication:
 That's a db server activity, and there's no reason why a developer would
need to care. They are storing something, taking care of the data is
another issue.
Recover:
 DB issue again. This is why we use a DB to store the data, it does all
this for us :)
Non-OO:
 Then you're being silly... :-) But you write some struts and a database
interface layer to attempt the translation. Again, not an issue we care
about. Are you trying to say I should write OO code in a non OO manner,
just because somewhere in the world, some weirdo might wake up one day and
think, "I know, I'll try to access this data with a non-OO
environment". Well that doesn't bother me, if that's a requirement, you
write the system like that from the outset.

> And I return to my earlier point. If you want pure OO don't use a database.

Everyone uses a database to store stuff. OO works perfectly, and there are
thouands of examples we can look at. Personally, I've just spent five
years (about to move on) helping develop a Java system
(www.objectmatter.com) for my company to store hundreds of objects in a
DB. The databases are massive:

mysql> select count(*) from BillSummaryTag;
+----------+
| count(*) |
+----------+
|  9801390 |
+----------+

Superset mapping used, I hardly notice the time taken to retrieve an
object. That's why we use SQL and databases, and with the world virtually
stuck on OO, that's the future.

At the end of the day, if you think we shouldn't use a database to store
OO, then perhaps we should all write in PL/SQL ;-)

I haven't had the pleasure of DB2 yet - I'm looking forward to it :)



John

[SQLObject] Pool count

[Frank B. <fb...@fo...>]

Hallo,

I added something like:

            print "self._pool: %s " % len(self._pool)

to DBConnection's getConnection and releaseConnection methods. This
count seems to only go up constantly: 
...
self._pool in get: 12 
self._pool in release: 13 
self._pool in get: 12 
self._pool in release: 13 
self._pool in release: 14
...

Is this expected behaviour? 

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] Join tables

[Andy T. <an...@ha...>]

John Baker wrote:

> On Tue, Nov 11, 2003 at 10:04:56AM +0000, Andy Todd wrote:
> 
>>John Baker wrote:
>><rant>
>>I'm not. I'm writing an application which stores its data in a 
>>relational database but which is written in an object oriented style. If 
>>I wanted an object persistence mechanism I'd use something like ZODB or 
>>PyPerSyst.
>>
>>This is something that irks me when working with colleagues who have 
>>limited exposure to technologies other than, for instance, Java. They 
>>are always in conflict with their DBAs (who do still exist btw) 
>>justifying data models where one side is simply using the db as a 
>>persistence mechanism and the other is trying to use the database 
>>'properly'.
>>
>>In general, the right solution is somewhere in the middle. Some of the 
>>more esoteric object associations don't map nicely to databases and code 
>>has to be written in your application to accomodate them. Likewise you 
>>can't always have a third normal form relational model in your database, 
>>sometimes you have to live with a little duplication or non-optimal 
>>storage structures to make the application easier to code. Its all about 
>>compromise
>></rant>
>>
>>Sorry about that, but *I* feel better now.
> 
> 
> That's ok :-)
> 
> DBAs are a dieing breed. After all, who needs a DBA for MySQL or
> Postgres? Alright, so you may be using Oracle, and I pity you :)

Well I still get called one occasionally, but in reality I'm what Scott 
Ambler calls a 'generalising specialist' 
(http://www.agiledata.org/essays/becomingAgile.html). Never mind Oracle 
(all hail Larry), have you tried using DB2? Oracle is great compared to 
that lumbering dinosaur. Regardless, though, you still need to apply 
rigour and method to your use of a database be it MySQL, Oracle, DB2 or 
even SQLite. You may want to call them DBA tasks, I call them 
development activities.

> 
> If SQLObject is to be aimed at a very limited set of solutions, that don't
> lend themselves towards proper OO programming, then perhaps clearly
> stating this on the website would be a step forward. But at this point in
> time, without superset mapping, you cannot model anything more than
> trivial relationships. This makes it rather unhelpful, which is a shame
> because it's actually quite easy to use.

Very limited? Virtually every system I come across these days, both 
professionally and in my own time, has the application code written in 
an OO language and the data stored in a relational database. I don't 
believe that this is entirely because everyone has these Oracle (or 
Sybase or MySQL) licenses lying about and says "Oh, I know, lets use 
this for storing our objects". Its because the relational database 
supplies benefits over and above storing your objects in flat files. 
Otherwise everyone would be using shelve, right?

> 
> Databases are there to store data. They should do no more. I do not
> subscribe to the Oracle approach of, "Let's throw lots of crap into a
> product [nat/firewall/multiple redo files]" in a desperate attempt to
> provide a reason for an upgrade. I've got a bunch of databases, some of
> which have tables that are a mere 500megs, mapped using superset
> mappings. I see no performance issues, although I am using MySQL :)

And I come to the situation from the other side, if you are *just* using 
a databsae to store data then why not just get a decent journalled file 
system on a RAID disk system and use flat files. That way you aren't 
constrained by Mr Codd's nasty rules.

> 
> To the developer, how the data is stored is totally irrelevant. But when
> your developer can't actually model relationships because the system of
> persistence is restrictive, then we have a problem. I've already provided
> two or three examples that clearly demonstrates that SQLObject can't do
> what I want it to do, and I'm hardly trying :-)

Correct. But the developer isn't the only actor in this drama. What 
about extracting data from an operational system to a data warehouse? Or 
writing summary reports on working data sets? Or replication? Or 
point-in-time recovery? Or, shock horror - non OO access to your data?

> 
> It's not my project, and superset mappings would be tricky to
> implement. But as it is, assuming you can't have this type of mapping, the
> foundations of the project are extremely limited.
> 
> And as we all, as good OO developers know, if you build on poor
> foundations, you regret it later in life.

And I return to my earlier point. If you want pure OO don't use a database.

> 
> 
> John

I'm not trying to start an argument here, its plain from our posts that 
we have slightly different points of view. I for one am just happy to 
agree to disagree. Its of benefit to the community (at large and the 
users of SQLObject) to have people with as broad a variety of 
backgrounds (and technical inclinations) as possible.

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/

Re: [SQLObject] Join tables

[John B. <jb...@dr...>]

On Tue, Nov 11, 2003 at 10:04:56AM +0000, Andy Todd wrote:
> John Baker wrote:
> <rant>
> I'm not. I'm writing an application which stores its data in a 
> relational database but which is written in an object oriented style. If 
> I wanted an object persistence mechanism I'd use something like ZODB or 
> PyPerSyst.
> 
> This is something that irks me when working with colleagues who have 
> limited exposure to technologies other than, for instance, Java. They 
> are always in conflict with their DBAs (who do still exist btw) 
> justifying data models where one side is simply using the db as a 
> persistence mechanism and the other is trying to use the database 
> 'properly'.
> 
> In general, the right solution is somewhere in the middle. Some of the 
> more esoteric object associations don't map nicely to databases and code 
> has to be written in your application to accomodate them. Likewise you 
> can't always have a third normal form relational model in your database, 
> sometimes you have to live with a little duplication or non-optimal 
> storage structures to make the application easier to code. Its all about 
> compromise
> </rant>
> 
> Sorry about that, but *I* feel better now.

That's ok :-)

DBAs are a dieing breed. After all, who needs a DBA for MySQL or
Postgres? Alright, so you may be using Oracle, and I pity you :)

If SQLObject is to be aimed at a very limited set of solutions, that don't
lend themselves towards proper OO programming, then perhaps clearly
stating this on the website would be a step forward. But at this point in
time, without superset mapping, you cannot model anything more than
trivial relationships. This makes it rather unhelpful, which is a shame
because it's actually quite easy to use.

Databases are there to store data. They should do no more. I do not
subscribe to the Oracle approach of, "Let's throw lots of crap into a
product [nat/firewall/multiple redo files]" in a desperate attempt to
provide a reason for an upgrade. I've got a bunch of databases, some of
which have tables that are a mere 500megs, mapped using superset
mappings. I see no performance issues, although I am using MySQL :)

To the developer, how the data is stored is totally irrelevant. But when
your developer can't actually model relationships because the system of
persistence is restrictive, then we have a problem. I've already provided
two or three examples that clearly demonstrates that SQLObject can't do
what I want it to do, and I'm hardly trying :-)

It's not my project, and superset mappings would be tricky to
implement. But as it is, assuming you can't have this type of mapping, the
foundations of the project are extremely limited.

And as we all, as good OO developers know, if you build on poor
foundations, you regret it later in life.


John

Re: [SQLObject] Join tables

[Andy T. <an...@ha...>]

John Baker wrote:

> On Monday 10 November 2003 21:54, Ian Bicking wrote:
> 
> 
[snip]
> 
> There's no reason why they can't be aware, in the form of a "key" column. But 
> stop worrying about that, you're writing an object storage system. The person 
> writing objects to map to a database shouldn't and won't care about all of 
> this. They shouldn't even have to know SQL, all they care about is using a 
> system that persists objects. 
> 
> In a perfect world, the system of storage is irrelevant to the person writing 
> the code. Files/SQL/ObjectDatabases/SmokeSignals/Pigeons. It doesn't matter, 
> the magic should be nothing more than a transparent interface to storage and 
> retrieval, without them having to worry about "not being able to do this 
> because the mapping tool doesn't support superset mappings".
> 
> I realise the world isn't perfect. :)
> 
[snip]
> 

<rant>
I'm not. I'm writing an application which stores its data in a 
relational database but which is written in an object oriented style. If 
I wanted an object persistence mechanism I'd use something like ZODB or 
PyPerSyst.

This is something that irks me when working with colleagues who have 
limited exposure to technologies other than, for instance, Java. They 
are always in conflict with their DBAs (who do still exist btw) 
justifying data models where one side is simply using the db as a 
persistence mechanism and the other is trying to use the database 
'properly'.

In general, the right solution is somewhere in the middle. Some of the 
more esoteric object associations don't map nicely to databases and code 
has to be written in your application to accomodate them. Likewise you 
can't always have a third normal form relational model in your database, 
sometimes you have to live with a little duplication or non-optimal 
storage structures to make the application easier to code. Its all about 
compromise
</rant>

Sorry about that, but *I* feel better now.

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/

Re: [SQLObject] Join tables

[Andy T. <an...@ha...>]

Ian Bicking wrote:

> On Nov 10, 2003, at 2:42 PM, John Baker wrote:
> 
>> class Area (SQLObject):
>>
>> class Office (Area):
>>
>> class Home (Area):
>>
>> SQLObject creates three tables, with a foreign key from Home -> Area and
>> Office -> Area.
>>
>> This means I can do:
>>
>> class Person (SQLObject):
>>   areas = ReferenceJoin("Area"...)
>>
>> and map a Person to a number of Areas, without having to map him to 
>> Office and
>> Home via two join tables.
>>
>> Someone said superset mapping wouldn't be happening, which is a shame, 
>> as it's
>> the most obvious and logical choice of the majority of OO mapping.
> 
> 
> That was probably me.  I find it difficult to map between class 
> hierarchies and tables, and I don't like the ambiguity or arbitrariness 
> of how that mapping has to happen.
> 
> Which isn't to say I'd be opposed to superset mapping, I'd just rather 
> that it not look like Python inheritance.  To me it's more of an 
> implicit join.  Or the folding together of multiple tables.  Or... I 
> don't know.  When I see a metaphor that looks better, and hopefully 
> doesn't involve terms (or even concepts) like "implicit" or "folding", 
> then maybe I'll feel more enthusiastic.  I'm trying to avoid magic to 
> the degree possible, which is where my reluctance comes from.
> 
> -- 
> Ian Bicking | ia...@co... | http://blog.ianbicking.org
> 

In relational data modelling this is implemented via an 'arc' 
relationship. At the logical level the relationship can be to either the 
supertype (area) or one of its subtypes (home or office).

When this is translated to a physical model you, usually, end up with 
one table with the same name as the super type. This table then has a 
'type' column which indicates what sort of sub-type it is. Then all of 
the foreign keys can be to the super type table and any further 
restrictions (e.g. this fk is only for this specific sub-type) can be 
enforced with constraints.

Unless, of course, you do it the other way round ;-) But implementing 
each of the subtypes as its own table brings many more problems (as 
mentioned by the op) and is only really a good idea when the sub types 
have very little in common - in which case are they really sub types?

All of which is fine until you introduce objects, with their 
associations and inheritance which are just about close to what I've 
described, but not quite close enough. So how this should work in an 
object relational mapper I leave to more experienced heads.

Regards,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/

[SQLObject] Reversed ordering, again

[Randall R. <ra...@ra...>]

For Googlers:

In SQLObject 0.5, using psycopg, you can't reverse the
orderBy of the column 'id'.  You can reverse any column
you explicitly specified by the name you gave for the
column.  So this:

MyClass.select().reversed()

doesn't actually work, but you can work around it by
including another autoincrement column, or a DateTime
column that uses the SQLBuilder.func.NOW() function
as its default.  If you're like me, you use that in
most objects anyway, just in case you might want to
track creation times later. :)

In the documentation for 0.5, this paragraph:

"""
You can use the keyword arguments orderBy and groupBy to
create ORDER BY and GROUP BY in the select statements:
orderBy takes a string, which should be the database
name of the column, or a column in the form Person.q.firstName;
groupBy is similar. Both accept lists or tuples of arguments.
You can use "-colname" to specify descending order, or call
MyClass.select().reversed().
"""

is wrong as written, and should be

"""
You can use the keyword arguments orderBy and groupBy to
create ORDER BY and GROUP BY in the select statements:
orderBy takes a string, which should be the attribute
name, not the database column name.  Unlike in the select()
method, you cannot use columns in the form Person.q.firstName
here; [...] You can use "-attributeName" to specify
descending order; MyClass.select().reversed() is not
implemented (or broken) and returns None.
"""

I didn't test groupBy, so omitted that part of this
paragraph.

It may be that these are considered bugs, or simply weren't
yet implemented as documented; I don't know.  The lack of
response the other day suggests the latter. :)

--
Randall Randall
ra...@ra...

Re: [SQLObject] Join tables

[John B. <jb...@dr...>]

On Monday 10 November 2003 22:02, Nick wrote:

> I think one of the real advantages of SQLObject's approach is that the
> database schema is still generally useful to non-Python/SQLObject
> interactions.  Otherwise you'd be stuck with a particular schema
> paradigm that may not be easily/sanely implemented in a different
> language.  And it could be a nightmare for your DBA who may have to
> tweak data (let's face it, your PHB will make you do it) using a
> database browser or SQL command line.

There's no reason why it can't support many mapping types. Superset and (I'll 
call the current one) Subset are both perfectly valid.

I care about writing code that you know is wrong, and I'm certainly not going 
to stop a DBA (if those people still actually exist ;-) me doing that. :)


-- 
John Baker,
(m) 07736393822
http://rant.pointful.info

Re: [SQLObject] Join tables

[John B. <jb...@dr...>]

On Monday 10 November 2003 21:54, Ian Bicking wrote:

> And this is just what makes me nervous.  What does the Item table look
> like?  How does it relate to the rest of the system, and how does it
> grow?  Presumably Bill and other classes are keyed off of item_id, but
> how do you know which kind of Item you are working with?

You don't. That's how it starts to become efficient. However a common method 
is to add a 'filter' column to your top level table to say what type of 
object it is.

> Superclasses shouldn't have to be aware of subclasses, but I don't see
> how that can happen with this system.  Also, you get all sorts of
> weirdness in class creation -- essentially Item.__new__ will have to
> return a Bill instance, or some other instance, depending on the ID.

There's no reason why they can't be aware, in the form of a "key" column. But 
stop worrying about that, you're writing an object storage system. The person 
writing objects to map to a database shouldn't and won't care about all of 
this. They shouldn't even have to know SQL, all they care about is using a 
system that persists objects. 

In a perfect world, the system of storage is irrelevant to the person writing 
the code. Files/SQL/ObjectDatabases/SmokeSignals/Pigeons. It doesn't matter, 
the magic should be nothing more than a transparent interface to storage and 
retrieval, without them having to worry about "not being able to do this 
because the mapping tool doesn't support superset mappings".

I realise the world isn't perfect. :)

> What looks like inheritance isn't really inheritance, it's something
> else.  I don't know what to call it, but I don't want to create a false
> cognate by phrasing it incorrectly -- especially when its in relation
> to something with the complex semantics of a Python class.

Think OO! Why do we even bother with object structures? We bother to separate 
functionality and appropriate variables. You can't just clump it all together 
in lots of different tables. 

Unless you're writing a noddy system, but once you get complex (unique keys, 
mapping Person --* Area), it starts to fall apart.

> I can appreciate this sentiment, but I'm also wary of it.  There are
> other Python ORMs that do better at distinguishing between business
> logic and persistence logic, but SQLObject is coming from a bit
> different perspective -- it's mapping tables to Python objects, not
> mapping Python classes to tables.  Ultimately I am unconvinced that you
> can get the persistence for free, that you can map arbitrary Python
> onto a DBMS -- at least without making the DBMS pointless.  Now, that's
> not to say SQLObject won't move more towards being a "seamless"
> persistency for objects; I have several ideas that could help improve
> this.  But I don't even know what "seamless" really means in this
> context -- no seamless persistence *can* exist in Python, because the
> status-quo is non-persistence (which will continue to be a viable
> option for a large number of objects and situations).
>
> But I'm wandering off onto tangents.

No, they are valid points. Seamless is where you want to be. But if you carry 
on along the path of "we won't do superset" then your building on foundations 
that won't hold very much. Better to think about it now, than have to start 
again later.

I've been through all of this with Objectmatter .. :-)

I don't think superset mappings will be easily to implement at this stage, but 
it will be even harder 12 months down the line. :) Luckily, I won't be using 
Python for anything complex until I do see an object mapping tool going in 
this direction. I guess I could have picked something else, but SQLObject can 
just about cope with my 'not very complex' case. Yet it's already quite ugly:


# All of our database object definitions
class Area (SQLObject):
  _connection = getConnection()

  areaId = StringCol()
  title = StringCol()
  description = StringCol()

  # Removing makes for not so nice OO
#  contacts = RelatedJoin('Contact', joinColumn='area',
#    otherColumn='contact', intermediateTable='AreaContactJoin')
#  branches = RelatedJoin('Branch', joinColumn='area',
#    otherColumn='branch', intermediateTable='AreaBranchJoin')
#  events = RelatedJoin('Event', joinColumn='area',
#    otherColumn='event', intermediateTable='EventAreaJoin')

class Ward (Area):
  _connection = getConnection()

  # Should be in parent class if nice OO
  contacts = RelatedJoin('Contact', joinColumn='ward',
    otherColumn='contact', intermediateTable='WardContactJoin')
  branches = RelatedJoin('Branch', joinColumn='ward',
    otherColumn='branch', intermediateTable='WardBranchJoin')
  events = RelatedJoin('Event', joinColumn='ward',
    otherColumn='event', intermediateTable='WardEventJoin')

class Constituency (Area):
  _connection = getConnection()

  description = StringCol()
  sittingMP = ForeignKey("Contact")

  # Should be in parent class if nice OO
  contacts = RelatedJoin('Contact', joinColumn='const',
    otherColumn='contact', intermediateTable='ConstContactJoin')
  branches = RelatedJoin('Branch', joinColumn='const',
    otherColumn='branch', intermediateTable='ConstBranchJoin')
  events = RelatedJoin('Event', joinColumn='const',
    otherColumn='event', intermediateTable='ConstEventJoin

Spot the repeated code. And when you're forced to do that, what you're doing 
is wrong. :)


John

-- 
John Baker,
(m) 07736393822
http://rant.pointful.info

Re: [SQLObject] Join tables

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

On Mon, 2003-11-10 at 15:54, Ian Bicking wrote:
> I can appreciate this sentiment, but I'm also wary of it.  There are 
> other Python ORMs that do better at distinguishing between business 
> logic and persistence logic, but SQLObject is coming from a bit 
> different perspective -- it's mapping tables to Python objects, not 
> mapping Python classes to tables.  Ultimately I am unconvinced that you 
> can get the persistence for free, that you can map arbitrary Python 
> onto a DBMS -- at least without making the DBMS pointless.

I think one of the real advantages of SQLObject's approach is that the
database schema is still generally useful to non-Python/SQLObject
interactions.  Otherwise you'd be stuck with a particular schema
paradigm that may not be easily/sanely implemented in a different
language.  And it could be a nightmare for your DBA who may have to
tweak data (let's face it, your PHB will make you do it) using a
database browser or SQL command line.

Nick